MrExcel Publishing
Your One Stop for Excel Tips & Solutions

countif(and(


Posted by Lori on November 16, 2001 12:26 PM

can COUNTIF be combined with the AND function? It seems like it should work, but I can't seem to get it. I want to count the contents of column b if the results are greater than 42,000 and column C is less than 2300. I tried this:
=countif(and(B3:B100,">42000",C3:C100,"<2300"),0)
Is this anywhere close to being right?
Thanks!


Posted by Aladin Akyurek on November 16, 2001 12:30 PM

Lori --

The answer is no. COUNTIF cannot do AND'ing.

Use instead:

=SUMPRODUCT((B3:B100>42000)*(C3:C100 < 2300))

Aladin

Posted by Lori on November 16, 2001 12:42 PM

Thank you so much, and thanks for the quick answer!