# COUNTIF for multiple criteria?

Posted by Mike Winters on December 01, 2001 9:43 AM

I have a formula:
=COUNTIF(G4:G83,"2LT")
which works fine, but I need it to also count all occurences of "1LT" and "CPT" in the same range, and return the total of all of them. I have tried using several variations of
=COUNTIF(G4:G83,"2LT" OR("1LT","CPT")), as well as some SUM and SUMIF/ SUM(IF formulas, but none work the way I want it to, they just give me erros. Any ideas on this one?

Sincerely,

Mike Winters
United States Army

Posted by Aladin Akyurek on December 01, 2001 11:13 AM

Mike

=COUNTIF(G4:G83,"1LT")+COUNTIF(G4:G83,"CPT")

or

=SUMPRODUCT((A14:A19={"1lt","cpt"})+0)

The first one is a less expensive formula.

=========

Posted by TD on December 01, 2001 11:27 AM

Since an OR statement doesn't work, you may have to compund the formula:

=COUNTIF(G4:G83,"2LT")+COUNTIF(G4:G83,"1LT")+COUNTIF(G4:G83,"CPT")

Posted by Mike Winters on December 01, 2001 12:15 PM

That works perfectly, thank you. I didn't realize that I could compund the formulas like that. That will change the way I do a lot of things in the future. Thanks again.

Sincerely,

Mike Winters
United States Army

Posted by Mike Winters on December 01, 2001 12:37 PM

Re: COUNTIF for multiple criteria in different columns

OK, now I am trying to count all the occurences of "CPT" in column G, but only the ones that also have "African American" in column J.
I tried
=COUNTIF(G4:G110,"CPT")*AND(J4:J110,"African American")
but I get a #VALUE! error in the cell. I know I am close to the answer, I just can't figure out the proper syntax. Any ideas?

Sincerely,

Mike Winters
United States Army

Posted by Aladin Akyurek on December 01, 2001 12:53 PM

Re: COUNTIF for multiple criteria in different columns

=SUMPRODUCT((G4:G110,"CPT")*(J4:J110="African American"))

========

Posted by Mike Winters on December 01, 2001 1:13 PM

Re: COUNTIF for multiple criteria in different columns

When I put that in, Excel gave me the message "the formula you typed contains an error"
Strange, because it looks like it should work. I also tried entering it as an array formula, but got the same error. What am I doing wrong?

Sincerely,

Mike Winters
United States Army

Posted by Aladin Akyurek on December 01, 2001 1:19 PM

Re: COUNTIF for multiple criteria in different columns

Mea culpa. It should be:

=SUMPRODUCT((G4:G110="CPT")*(J4:J110="African American"))

You don't need to array-enter it.