Excel 2000 formula

[Guest]

I have 2 worksheets. The first worksheet contains data queried from an
Access 2000 database. Fields as follows:

(Column A) Date/time: eg '05/04/2007 00:00'
(Column C) Category: eg 'A1' or 'A2' or 'A3' etc
(Column D) Lost time: eg 'Yes' or 'No'

A cell in the second worksheet needs to count how many instances match all
the following criteria:

Category = A1
Date = 01/04/2007 to 30/04/2007
Lost time = Yes

Many thanks in advance for any help with this.

 

[JE McGimpsey]

one way:

=SUMPRODUCT(--(A1:A1000>=DATE(2007,4,1)),
--(A1:A1000<=DATE(2007,4,30)), --(C1:C1000="A1"), --(D11000="Yes")))

You might also look at DSUM()

and if you want all the combinations (and you want to group dates by,
say, months or quarters), a Pivot Table is the way to go.

 

[Guest]

JE McGimpsey

Thank you so much. It works beautifully. Have a great day...!