Formula help needed please

[Bob Newman]

Excel 2010. I have a database containing one line for each date and several columns corresponding to that date. I am trying to create a table that shows the totals in each column for each WEEK of 2013. ie the totals for Jan. 1 through January 7 and so on. I know the sumif formula but am not surehow to total it on a weekly basis. Any help would be appreciated.

Thanks in advance... Bob

 

[Claus Busch]

Hi Bob,

Am Thu, 27 Dec 2012 09:47:12 -0800 (PST) schrieb Bob Newman:

Excel 2010. I have a database containing one line for each date and several columns corresponding to that date. I am trying to create a table that shows the totals in each column for each WEEK of 2013. ie the totals for Jan. 1 through January 7 and so on. I know the sumif formula but am not sure how to total it on a weekly basis. Any help would be appreciated.

your date in column A, your values in column B then in C2:
=SUM(IF($A$2:$A$367<>"",(TRUNC(($A$2:$A$367-WEEKDAY($A$2:$A$367,2)-DATE(YEAR($A$2:$A$367+4-WEEKDAY($A$2:$A$367,2)),1,-10))/7)=ROW(A1))*(YEAR($A$2:$A$367)=2012)*$B$2:$B$367))
Enter the array formula with CTRL+Shift+ Enter and copy down


Regards
Claus Busch

 

[Living the Dream]

You could also try it this way:

Column A = Your Dates
Column B - Week No. =TRUNC((($A2-DATE(YEAR($A2),1,0))+6)/7)
Column C = Your Values

Anywhere in your workbook you can have something like the following:

Week .1 =SUMPRODUCT(($B$2:$B$367=1)*($C$2:$C$367))
Week .2 =SUMPRODUCT(($B$2:$B$367=2)*($C$2:$C$367))
Week .3 =SUMPRODUCT(($B$2:$B$367=3)*($C$2:$C$367))
.....
....
...
..
Week .53 =SUMPRODUCT(($B$2:$B$367=53)*($C$2:$C$367))

An extra week, just in case...

HTH
Mick.