range checking

[kev carter]

hi

i have a worksheet with a lot of data on it
column A contains the date
column B contains an area
column C contains the fault
column D contain a quantity


A B C
D
1/4/04 stores damaged
10
7/4/04 bins rust
60

what i want to do is return quantity for a date range(one week) that is
both damaged and in the stores or any other combination
there are 10 areas and 12 faults plus 52 weeks this is a lot of formulas

anyone got any ideas how i can
do this over a range


Thanks in advance


kevin

 

[Frank Kabel]

Hi
try something like
=SUMPRODUCT(--(A1:A100>=DATE(2004,4,1)),--(A1:A100<=DATE(2004,4,7)),--(
B1:B100="stores"),--(C1:C100="damaged"),D1100)

 

[kev carter]

thanks Frank
this would mean i have to enter a formula for each condition
i have 10 areas and 12 faults for each week of the year
is there any way of reducing the number of formulas ?