x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 66

# Sum by Week

Experts,

What would be the formula to sum by week?
Weekly-Sum.xlsx
0
pdvsa
1 Solution

Excel & VBA ExpertCommented:
One way is to create a helper row#3 and then use the following formula in A7 and copy across.
``````=IF(A3=B3,"",SUMPRODUCT((\$A\$3:\$R\$3=A3)*\$A\$2:\$R\$2))
``````
Weekly-Sum.xlsx
1

Commented:
Hi, you can use a combination of weeknum (for which you need Analysis Toolpak) to correctly get the week number of each date entry and then a sumif formula (the weeknum formula can't be used with the SUMPRODUCT formula suggested above).

I suggest doing the following.
You have dates in Row1 and amounts in row 2.
In row 3 create =WEEKNUM(A1) [ copy across this as far as needed ]
In row 4, list all calender weeks (i.e., 1,2,3,4,....) via AutoFill.
In row 5, enter: =SUMIF(\$A\$3:\$R\$3,A4,\$A\$2:\$R\$2) [ copy across as far as needed ]

Hope this helps!
Weekly-Sum_example.xlsx
0

Group Finance ManagerCommented:
Analysis ToolPak formulas are included in Excel's Functions now, no need to install it.

If the data was vertical instead of horizontal then a PivotTable would be a good solution
0

Project financeAuthor Commented:
Nice...very nice.  Thank you!
0

Excel & VBA ExpertCommented: