# Excel countifs criteria neither this nor that

Im trying to make a criteria for COUNTIFS such that the value of the cell is neither "pass" nor "fail" nor blank. How can I do this? I dont think "<>pass<>fail<>*" works

Since someone wanted this... I dont want to count the same things 3 times over. Thats kind of silly.

-

Just make three separate conditions:

=COUNTIFS(\$A\$1:\$A\$10,"<>pass",\$A\$1:\$A\$10,"<>fail",\$A\$1:\$A\$10,"*")
-
But then I have to iterate through all the cells three times! This sheet might get VERY big, and this would not be efficient. – David Grinberg Mar 4 '14 at 18:38
If that is your concern, include it in your post. – Excellll Mar 4 '14 at 18:44
@Dgrin91 : You don't actually know that it's any less efficient (internally to Excel). It may not look pretty, but it is the way COUNTIFS works: 3 criteria=3 ranges, even if ranges are repeated. There are alternate methods for getting your end result (e.g. helper columns), but you only asked how to do it in COUNTIFS. – Madball73 Mar 4 '14 at 19:06
@Dgrin91 FYI, this calculates ~instantaneously for >600,000 test records to search through. You only need to worry seriously about inefficiency if you are using thousands of these COUNTIFS formulas. – Excellll Mar 4 '14 at 19:13

Here's an alternative. It's an array* formula though, and I'll wager it's slower than COUNTIFS

=SUM(N(IFERROR(MATCH("x"&\$A\$1:\$A\$10,{"xpass","xfail","x"},0),0)=0))

The "x" kludge is because I couldn't figure out how else to get the "" condition to count properly.

Array* formula: commit with Ctrl+Shift+Enter, not just Enter.

-