Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

Just make three separate conditions:

share|improve this answer
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


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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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