COUNTIF STATEMENTS
 

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

COUNTIF STATEMENTS
 

Use SUMPRODUCT when you have multiple criteria:

=SUMPRODUCT(--(A1:A5=1500),--(B1:B5DATE(2009,1,3)))

HTH
Elkar


"Rj" wrote:

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

COUNTIF STATEMENTS
 

Not COUNTIF but SUMPRODUCT
=SUMPRODUCT(--(A1:A20=1500),--(B1:B20DATE(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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rj" wrote in message
...
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