• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • Last Modified:

Sum by Week

Experts,

What would be the formula to sum by week?  
Please see attached.
Weekly-Sum.xlsx
0
pdvsa
Asked:
pdvsa
1 Solution
 
Subodh Tiwari (Neeraj)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))

Open in new window

Weekly-Sum.xlsx
1
 
xtermieCommented:
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
 
Roy CoxGroup 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
 
pdvsaProject financeAuthor Commented:
Nice...very nice.  Thank you!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
Thanks for the feedback.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now