Summarizing daily data in columns by week

[GKlucsarits]

Hi All,

I've been searching the topics but haven't found a simple solution to my challenge and am hoping that someone might have a better technique.


I have a table that has been used in the company to collect daily data for one of our accounts. The first column contains the different metrics, e.g. "total orders", "shipped on time", etc. Each subsequent column is a date, e.g. "7 Mar 2011", "8 Mar 2011", etc.


There are a large number of rows of data, approximately 40, and the number of columns usually represents a full quarter.


I've been going through Chandoo's course and working on developing a straightforward dashboard to automatically summarize and update the metrics.


My challenge is that I need to summarize the data by week. For instance, total orders in the seven columns representing this week would be rolled up into a single number of total orders for the week ending 13 March.


I would like to keep the current table as it is and have the weekly summary on another worksheet. The other worksheet would have the same categories in the first column but the row headers would now represent the week ending date rather than each day.


Any suggestions are most welcome. Thanks in advance,


George

 

[Hui]

GKlucsarits

You have lots of options here

Use formulas or use a Pivot table


Formulas:

Assuming your dates are in Row 1

and your data is in row 3


You can use something like

=SUMPRODUCT((C1:CZ1>=DATE(2011,5,1))*(C1:CZ1<=DATE(2011,5,7)),(C3:CZ3))

=SUMPRODUCT((C1:CZ1>=A1)*(C1:CZ1<=(A1+7)),(C3:CZ3)) If A1 has a date in it


=SUMIFS(C3:CZ3,C1:CZ1,">="&DATE(2011,5,1),C1:CZ1,"<="&DATE(2011,5,7))

=SUMIFS(C3:CZ3,C1:CZ1,">="&A1,C1:CZ1,"<="&A1+7)


Change the dates and ranges to suit

For more on Sumproduct: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

or http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

For more on Sumifs: http://chandoo.org/wp/2010/04/20/introduction-to-excel-sumifs-formula/


Pivot Table:

I would transpose your data so that you have dates down Column A and the fields across the top row and then take it all into a pivot table

and then group the data by the date Filed and display as Weeks


for more on Pivot Tables: http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/

 

[GKlucsarits]

Hi Hui,


Thanks for the speedy response; I'll try the SUMPRODUCT option as soon as I get to work.


I agree that transposing the data would be best and I will try to implement that for a future iteration. The current format is a bit of a legacy and changing it will require some additional changes and training; the same format is used in two other sites in Asia and Europe and major rework would require some customer approval as well.


Cheers,


George