Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: COUNTIF for multiple criteria?

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

=========


Re: COUNTIF for multiple criteria?

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")


Re: COUNTIF for multiple criteria?

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


Re: COUNTIF for multiple criteria in different columns

Posted by Mike Winters on December 01, 2001 12:37 PM
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


Re: COUNTIF for multiple criteria in different columns

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

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

Aladin

========


Re: COUNTIF for multiple criteria in different columns

Posted by Mike Winters on December 01, 2001 1:13 PM
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


Re: COUNTIF for multiple criteria in different columns

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

Mea culpa. It should be:

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

You don't need to array-enter it.

Aladin


Thank You!

Posted by Mike Winters on December 01, 2001 1:29 PM
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.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.