re post range check

[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"),D1100)
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

 

[Frank Kabel]

Hi
try using a pivot table then:
- use your columns as row items
- group by the date

See the following sites:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[kevin carter]

Frank
thankyou for your reply
i have never used a pivot table
but tried very quickly this morning and run in to trouble(got myself
confused)