Solved

Sum by Week

Posted on 2016-09-01
5
52 Views
Last Modified: 2016-09-01
Experts,

What would be the formula to sum by week?  
Please see attached.
Weekly-Sum.xlsx
0
Comment
Question by:pdvsa
5 Comments
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41779550
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
 
LVL 17

Expert Comment

by:xtermie
ID: 41779797
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
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41780251
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
 

Author Closing Comment

by:pdvsa
ID: 41781061
Nice...very nice.  Thank you!
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41781065
You're welcome. Glad to help.
Thanks for the feedback.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now