# Compressing a COUNTIFS formula

Trying to help a friend with a really simple `COUNITF/COUNTIFS` formula. Data is like: 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.

``````=SUMPRODUCT(--(ISERROR(MATCH(\$A\$2:\$A\$13,B:B,0))))
`````` or just subtract from the whole:

``````=COUNTA(A2:A13)-SUM(COUNTIFS(A2:A13,B2:B4))
`````` • THANK YOU.......................it's an approach I never would have considered. – Gary's Student Mar 23 at 18:30