lookups and sums?

[jb]

Need help converting daily data into weekly sums.
We have data from 25 offices - 7 rows of daily data from each of the 25
offices (1 row for each day of the week) totalling 175 rows of data. Each
row begins with the office name, followed by 132 columns of data. I need to
create a separate sheet with weekly data taken from the daily sheet. How do
I sum the daily data from each office in order to create a separate weekly
sheet? It should look the daily sheet except it's weekly data. It would be
25 rows, a row for each office, with the weekly data totaled in each column.
Have been experimenting with lookups to no avail. Am trying to stay in the
formulas available in excel.
Thank you for any help with this!

 

[Jacob Skaria]

With data arranged in Sheet1 as below

Col A Col B Col C
Name Date Amount
Office 1 11/11/2009 1
Office 1 11/12/2009 2
Office 1 11/13/2009 3

In Sheet2 with data arranged as below
Col A Col B Col C
Name Week starting total
Office 1 11/11/2009 21
Office 1 11/18/2009 15

In Sheet2 Col C2; the weekly total formula for the weekstarting 11/11/2009
for Office1 from sheet1 ColC
=SUMPRODUCT(((Sheet1!$A$2:$A$100=A2)*(Sheet1!$B$2:$B$100)>=B2)*
(Sheet1!$B$2:$B$100<=(B2+6)),Sheet1!$C$2:$C$100)

Once you are able to work this out..change the formula to suit other
columns...

If this post helps click Yes