help with a formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to take information from 1 sheet and insert it in a summary on a
seperate sheet. My information is recorded with date entries (daily) in
column a and amounts in column b. I need to record week ending total and
month ending totals. Column a and b could vary from 1 to 1000 entries. Any
ideas???

eg
column a column b
1/1/2007 20
2/1/2007 45
7/1/2007 9

Thanks
 
Hi

One way
Assuming you have the Analysis Toolpak loaded (Tools>Addins>check Analysis
Toolpak)
In column C enter
=WEEKNUM(A1) and copy down
In column D enter
=MONTH(A1) and copy down

On sheet2 in A1 enter No, in B1 Week Total, in C1 Month Total
In A2:A54 enter numbers 1, 2 etc.
In B1 enter
=SUMIF(Sheet1!C:C,A2,B:B)
copy down column B as far as week 53
in C1 enter
=SUMIF(Sheet1!D:D,A2,B:B)
copy down as far as month 12
 
Back
Top