use WEEKNUM to calculate weekly totals from daily entries?

you can use the sumif() function.

Like this:

In the first table is the daily information you enter. In this example, then date is in column A (but I didn't bother to enter that), column B continues the week number (which I made up), column C is th amount

The second table is a summary table where column A is the week numbers and B is the corresponding total for that week.

B2=SUMIF(Daily Information::$B, A2, Daily Information::$C)

this is shorthand for... select cell B2 and type (or copy and paste from here) the formula:

=SUMIF(Daily Information::$B, A2, Daily Information::$C)

select cell B2, copy

select cells B2 thru the end of column Bm paste

where you have WEEK# use

where you have 31 use:

row()-1+n

where n is the week number of the first row with a date

for the example you posted I suggest:

row()-1+26

Hi JMR,

Here is an approach that uses WEEKNUM() more extensively.

I think you already have this.

Your report SUMIF() can look like this:

The report table remains two columns and you don't need to define the week with more than the starting date.

quinn

The reason I did not propose that is because the year is implied in the OPs example and will generate different results if the they type in the whole year.

Here is another, more refined, method:

first two rows are header rows

Enter the starting date in the cell B1

B3=$B$1+"7d"×(ROW()−3)

select cell B3, copy

select cells B3 through the end of the column, paste

C3=B3+"6d"

select cell C3, copy

select cells C3 through the end of the column, paste

A3=WEEKNUM(B3)

fill A3 down like previous examples

D3=SUMIF(Daily Information::$B, A3, Daily Information::$C)

fill down like previous

Hi Wayne,

I don't follow you about the different results if the year is entered. In my table it doesn't seem to care.

I have become a massive fan of index columns. Esentially that is what our week# columns are. I have been running Numbers on a iPod Touch 5g and was able to optimize my tables by having index column(s) spread the calculation load. One index counted the number of times I saw different clients. I could use that to draw client info into appropriate rows. A glance actually gave me useful info too. Could never have gotten there without this forum.

quinn

what I'm saying is that when you enter a date like this "6/21" the year is implied to be "the year at the time you typed in the date" (so 2015). In this case things match up because the OP was actually intending (as evidence of the week being 26) the year to be 2015 but for a summary the year may not be 2015. That's all

if the year the OP intended was 2014, then 6/21/2014 will be a different week number than 6/21/2015 and sum the wrong week.

We are both advocating an index column

Hi Wayne,

I get what you are saying about running a setup like this over multiple years. I have tended to restart similar records every year so haven't had to tweak the week index.

Yes, we are calling our index "week number".

quinn

Hello

Here's some tables you may explore.

E.g. 1. Data table is sort-safe only in Numbers v3. Summary table is sort-safe in both Numbers v2 and v3. Week starts with Monday.

Data (excerpt) A1 date A2 2015-07-10 A3 2015-07-11 A4 2015-07-12 A5 2015-07-13 B1 amount B2 9 B3 2 B4 9 B5 11 C1 week C2 =WEEKNUM(A2,2) C3 =WEEKNUM(A3,2) C4 =WEEKNUM(A4,2) C5 =WEEKNUM(A5,2) D1 index D2 =IF(COUNTIF(C$2:C2,C2)=1,MAX($D$1:D1)+1,"") D3 =IF(COUNTIF(C$2:C3,C3)=1,MAX($D$1:D2)+1,"") D4 =IF(COUNTIF(C$2:C4,C4)=1,MAX($D$1:D3)+1,"") D5 =IF(COUNTIF(C$2:C5,C5)=1,MAX($D$1:D4)+1,"")

Summary (excerpt) A1 week start A2 =IF(LEN(D2)>0,D2-WEEKDAY(D2,3),"") A3 =IF(LEN(D3)>0,D3-WEEKDAY(D3,3),"") A4 =IF(LEN(D4)>0,D4-WEEKDAY(D4,3),"") A5 =IF(LEN(D5)>0,D5-WEEKDAY(D5,3),"") B1 week end B2 =IF(LEN(A2)>0,A2+6,"") B3 =IF(LEN(A3)>0,A3+6,"") B4 =IF(LEN(A4)>0,A4+6,"") B5 =IF(LEN(A5)>0,A5+6,"") C1 amount C2 =IF(LEN(E2)>0,SUMIF(Data::C,E2,Data::B),"") C3 =IF(LEN(E3)>0,SUMIF(Data::C,E3,Data::B),"") C4 =IF(LEN(E4)>0,SUMIF(Data::C,E4,Data::B),"") C5 =IF(LEN(E5)>0,SUMIF(Data::C,E5,Data::B),"") D1 date D2 =IF(LEN(E2)>0,INDEX(Data::A,MATCH(F2,Data::D,0),1),"") D3 =IF(LEN(E3)>0,INDEX(Data::A,MATCH(F3,Data::D,0),1),"") D4 =IF(LEN(E4)>0,INDEX(Data::A,MATCH(F4,Data::D,0),1),"") D5 =IF(LEN(E5)>0,INDEX(Data::A,MATCH(F5,Data::D,0),1),"") E1 week E2 =IFERROR(INDEX(Data::C,MATCH(F2,Data::D,0),1),"") E3 =IFERROR(INDEX(Data::C,MATCH(F3,Data::D,0),1),"") E4 =IFERROR(INDEX(Data::C,MATCH(F4,Data::D,0),1),"") E5 =IFERROR(INDEX(Data::C,MATCH(F5,Data::D,0),1),"") F1 index F2 1 F3 2 F4 3 F5 4

E.g. 2. Data table and Summary table are sort-safe in both Numbers v2 and v3. Week starts with Monday.

Data (excerpt) A1 date A2 2015-07-10 A3 2015-07-11 A4 2015-07-12 A5 2015-07-13 B1 amount B2 9 B3 2 B4 9 B5 11 C1 week C2 =WEEKNUM(A2,2) C3 =WEEKNUM(A3,2) C4 =WEEKNUM(A4,2) C5 =WEEKNUM(A5,2) D1 index D2 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C2)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"") D3 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C3)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"") D4 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C4)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"") D5 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C5)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"")

Summary (excerpt) * The same as E.g. 1.

Notes.

Row 1 is header row.

Formulae in row 2 can be filled down.

Tables are built with Numbers v2.

Regards,

H

Hello

In case, here're tables where week starts with Sunday.

E.g. 1A. Data table is sort-safe only in Numbers v3. Summary table is sort-safe in both Numbers v2 and v3. Week starts with Sunday.

Data (excerpt) A1 date A2 2015-07-10 A3 2015-07-11 A4 2015-07-12 A5 2015-07-13 B1 amount B2 9 B3 2 B4 9 B5 11 C1 week C2 =WEEKNUM(A2,1) C3 =WEEKNUM(A3,1) C4 =WEEKNUM(A4,1) C5 =WEEKNUM(A5,1) D1 index D2 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C2)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"") D3 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C3)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"") D4 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C4)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"") D5 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C5)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"")

Summary (excerpt) A1 week start A2 =IF(LEN(D2)>0,D2-MOD(WEEKDAY(D2,2),7),"") A3 =IF(LEN(D3)>0,D3-MOD(WEEKDAY(D3,2),7),"") A4 =IF(LEN(D4)>0,D4-MOD(WEEKDAY(D4,2),7),"") A5 =IF(LEN(D5)>0,D5-MOD(WEEKDAY(D5,2),7),"") B1 week end B2 =IF(LEN(A2)>0,A2+6,"") B3 =IF(LEN(A3)>0,A3+6,"") B4 =IF(LEN(A4)>0,A4+6,"") B5 =IF(LEN(A5)>0,A5+6,"") C1 amount C2 =IF(LEN(E2)>0,SUMIF(Data::C,E2,Data::B),"") C3 =IF(LEN(E3)>0,SUMIF(Data::C,E3,Data::B),"") C4 =IF(LEN(E4)>0,SUMIF(Data::C,E4,Data::B),"") C5 =IF(LEN(E5)>0,SUMIF(Data::C,E5,Data::B),"") D1 date D2 =IF(LEN(E2)>0,INDEX(Data::A,MATCH(F2,Data::D,0),1),"") D3 =IF(LEN(E3)>0,INDEX(Data::A,MATCH(F3,Data::D,0),1),"") D4 =IF(LEN(E4)>0,INDEX(Data::A,MATCH(F4,Data::D,0),1),"") D5 =IF(LEN(E5)>0,INDEX(Data::A,MATCH(F5,Data::D,0),1),"") E1 week E2 =IFERROR(INDEX(Data::C,MATCH(F2,Data::D,0),1),"") E3 =IFERROR(INDEX(Data::C,MATCH(F3,Data::D,0),1),"") E4 =IFERROR(INDEX(Data::C,MATCH(F4,Data::D,0),1),"") E5 =IFERROR(INDEX(Data::C,MATCH(F5,Data::D,0),1),"") F1 index F2 1 F3 2 F4 3 F5 4

E.g. 2A. Data table and Summary table are sort-safe in both Numbers v2 and v3. Week starts with Sunday.

Data (excerpt) A1 date A2 2015-07-10 A3 2015-07-11 A4 2015-07-12 A5 2015-07-13 B1 amount B2 9 B3 2 B4 9 B5 11 C1 week C2 =WEEKNUM(A2,1) C3 =WEEKNUM(A3,1) C4 =WEEKNUM(A4,1) C5 =WEEKNUM(A5,1) D1 index D2 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C2)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"") D3 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C3)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"") D4 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C4)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"") D5 =IF(COUNTIF(OFFSET(C$1,1,0,ROW()-1,1),C5)=1,MAX(OFFSET(D$1,0,0,ROW()-1,1))+1,"")

Summary (excerpt) * The same as E.g. 1A.

Regards,

H