count based on 2 criteria (date and status)

[Rusty]

I am trying to get the total number of items that match two criteria. The
first criteria is that the date is between 24/03/2007 and including
31/03/2007 (24-31 March 2007) and the second is that the status is Critical.

Date is in column F2 down to F30
Status is in column L2 down to L30

Column F----------Column L
Date Reported-----Status
29/03/2007--------Critical
5/03/2007----------Critical
25/03/2007---------Critical

From the above list I should get a result of 2.

Thanks

 

[T. Valko]

Try this:

=SUMPRODUCT(--(F2:F30>=DATE(2007,3,24)),--(F2:F30<=DATE(2007,3,31)),--(L2:L30="Critical"))

Better to use cells to hold the criteria:

A1 = 3/24/2007
B1 = 3/31/2007
C1 = Critical

=SUMPRODUCT(--(F2:F30>=A1),--(F2:F30<=B1),--(L2:L30=C1))

Biff

 

[Bob Flanagan]

Rusty, in an empty column, write an IF equation that uses AND(test, test,
test). For example:

=IF(AND(F3>=DATEVALUE("24/3/2007"),
F3<=DATEVALUE("31/3/2007"),L3="critical"),1,0)

This returns 1 if your conditions are met, 0 if not. Then just sum that
column

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


=if(AND(F2>=

 

[bpeltzer]

=sumproduct(--(f2:f30>=date(2007,3,24)),--(f2:f30<=date(2007,3,31)),--(l2:l30="Critical"))
Each of the three arguments of the sumproduct is an array of 1's (true) and
0's (false). Sumproduct first multiplies the three arrays together,
resulting in an array of 1s where all the criteria were met and 0s elsewhere,
then adds the array elements producing a count.

 

[Rusty]

Thanks to all of you for your quick Replies. It is now working perfectly.