sum downtime by week

[Peter]

can anyone tell me how to sum all downtime in a column depending on the
weeknum function

I have a list of dates in colum D

D1 = 2/2/04
D2 = 3/2/04
D3 = 6/2/04
D4 = 8/2/04

etc etc


I have all the downtime in column E

E1 = 14
E2 = 23
E3 = 45
E4 = 50

Etc etc

what I want is a total of the downtime for each week of the year


tia

Peter

 

[Frank Kabel]

Hi
one way (for the first week):
=SUMPRODUCT(--(WEEKNUM($D$1:$D$1000)=1),$E$1:$E$1000)
or use (if you start in row one:
=SUMPRODUCT(--(WEEKNUM($D$1:$D$1000)=ROW()),$E$1:$E$1000)
and copy down

another way:
- add a helper column (WEEKNUM) to your data and enter the formula:
=WEEKNUM(D1). Copy this down.
- Now create a pivot table on your data

 

[Bob Phillips]

Peter,

If it is standard weeks, then

=SUMPRODUCT((WEEKNUM(D11000)=1)*(E1:E1000))

and repeat for each week number.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)