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.
Aladin
=========
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"))
Aladin
========
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.
Aladin
Posted by Mike Winters on December 01, 2001 1:29 PM
Thank You!
Ahhh. That little comma messed it all up. Thank you for the help (and the sharp eye).
Sincerely,
Mike Winters
United States Army Mea culpa. It should be: =SUMPRODUCT((G4:G110="CPT")*(J4:J110="African American")) You don't need to array-enter it.
