4

Trying to help a friend with a really simple COUNITF/COUNTIFS formula. Data is like:

enter image description here

Objective is to count the data cells excluding mike, john, judy. Current formula:

=COUNTIFS(A2:A13,"<>mike",A2:A13,"<>john",A2:A13,"<>judy")

which returns the correct value 3. Problem is that he has much more than three names to exclude. We would like to list them in column B and have the formula refer to the list (otherwise the formula becomes huge). We have tried:

=SUM(COUNTIF(A2:A13,"<>B2:B4"))

both normal-entered and array-entered, but it yields 12 !

Strangely enough, if we want to include rather than exclude then :

=SUM(COUNTIF(A2:A13,B2:B4))

works. I offered a VBA solution, but that was rejected.

7

What about SUMPRODUCT:

=SUMPRODUCT(--(ISERROR(MATCH($A$2:$A$13,B:B,0))))

enter image description here

or just subtract from the whole:

=COUNTA(A2:A13)-SUM(COUNTIFS(A2:A13,B2:B4))

enter image description here

| improve this answer | |
  • THANK YOU.......................it's an approach I never would have considered. – Gary's Student Mar 23 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.