K
kevin 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
-- -- ---
--
-- --- - --
--
31-12-04 floor damp
100
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(it could be any
combination)
there are 10 areas and 12 faults plus 52 weeks this is a lot of formulas
i have had the formula below posted by Frank Kabel, this works fine for an
idividual event
=SUMPRODUCT(--(A1:A100>=DATE(2004,4,1)),--(A1:A100<=DATE(2004,4,7)),--(
B1:B100="stores"),--(C1:C100="damaged"),D1
100)
problem is i have 10 areas 12 faults and 52 weeks
that makes over 6000 formulas
is there a way either in VB code or in a formula to check the array over a
range
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
-- -- ---
--
-- --- - --
--
31-12-04 floor damp
100
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(it could be any
combination)
there are 10 areas and 12 faults plus 52 weeks this is a lot of formulas
i have had the formula below posted by Frank Kabel, this works fine for an
idividual event
=SUMPRODUCT(--(A1:A100>=DATE(2004,4,1)),--(A1:A100<=DATE(2004,4,7)),--(
B1:B100="stores"),--(C1:C100="damaged"),D1
100)problem is i have 10 areas 12 faults and 52 weeks
that makes over 6000 formulas
is there a way either in VB code or in a formula to check the array over a
range