Countif multiple criteria

[Michael Joiner]

I've tried some different countif formulas and array formulas, without success. I need a formula that says COUNTIF A2:A399 is NOT equal to "DETROIT" and is NOT equal to "PHILADELPHIA". Please help.

 

[delaneyjm]

Try:

=SUMPRODUCT(--($A$2:$A$399 <> "DETROIT"),--($A$2:$A$399 <> "PHILADELPHIA"))

 

[sulakvea]

simproduct is one option, but you can stick to countif only:

=398 - countif(A2:A399,"<>DETROID")-countif(A2:A399,"<>PHILLY")

 

[Special-K99]

Cant you just use COUNTIFS?

=COUNTIFS(A2:A399,"<>DETROIT",A2:A399,"<>PHILADELPHIA")

 

[EB08]

It depends on which version of excel Michael is using; i don't believe 'Countifs' was introduced until the 07 version of excel. If he's on 03, then the first response (sumproduct()) is the way i would go.

 

[Aladin Akyurek]

I've tried some different countif formulas and array formulas, without success. I need a formula that says COUNTIF A2:A399 is NOT equal to "DETROIT" and is NOT equal to "PHILADELPHIA". Please help.

Just enter:

=COUNTIF(A2:A399,"?*")-SUM(COUNTIF(A2:A399,{"DETROIT","PHILADELPHIA"}))

 

[delaneyjm]

Oh man, that's one quick formula Aladin.

 

[Michael Joiner]

Thank you very much for both examples (SUMPRODUCT, and COUNTIFS). I have 2003 at home and 2007 at work, so both are valuable. Mr Excel is my new BFF!

 

[delaneyjm]

The one posted by Aladin will perform the fastest for you, fyi