COUNTIF STATEMENTS

[Rj]

What function can I use to count the # of times something occurs using
multiple criteria? Where 'C1' would = the number of times A1:A5 = 1500 and
B1:B5 > 1/3/09. The answer should be 3, the # of times column A = 1500 and at
the same time column B > 1/3/09.


A B C
1 1500 1/2/09
2 1000 1/3/09
3 1200 1/5/09
4 1500 1/5/09
5 1500 12/4/08
6 1000 1/5/09
7 1500 2/5/09
8 1500 3/5/09

 

[Elkar]

Use SUMPRODUCT when you have multiple criteria:

=SUMPRODUCT(--(A1:A5=1500),--(B1:B5>DATE(2009,1,3)))

HTH
Elkar

 

[Bernard Liengme]

Not COUNTIF but SUMPRODUCT
=SUMPRODUCT(--(A1:A20=1500),--(B1:B20>DATE(2009,3,1))
I am assuming you use USA date format mm/dd/yyy; if not just reverse 1 and 3

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html