You can count all cells not equal to x or y using a simple COUNTIFS formula. If you only want to count using one condition, then you can also use COUNTIF as an alternative. I’ll explain both in this short tutorial.
Count cells not equal to x or y using COUNTIFS
The COUNTIFS syntax for this formula is relatively simple:
=COUNTIFS(range, "<>x", range, "<>y")
Now let’s look at an actual example. Let’s assume that we want to check a list of coffee orders and count the number of orders which were not for
- Type =COUNTIFS(
- Select the range or type the range name. In my example, I’ve used a named range called
- Insert the first condition as
"<>cappuccino". This tells Excel that we do not want to count any cell that has the text cappuccino.
- Select your date range again. Since we want to check two conditions for the same data set, we need to select the same range again.
- Insert the second condition, which in our case is
- Type ) and then press Enter to complete the COUNTIFS formula.
The final formula is:
=COUNTIFS(beverage, "<>cappuccino", beverage, "<>espresso")
Since the range
beverage is equal to E23:E42, the same formula can also be written as:
=COUNTIFS(E23:E42, "<>cappuccino", E23:E42, "<>espresso")
I strongly recommend that you download the file I’ve used when creating this tutorial.
How to use this COUNTIFS formula
Because we are looking to apply two conditions to our count formula, then we need to use COUNTIFS. The operator not equal to in Excel is written as
<>. For the formula to work properly, we need to concatenate the operator with the text condition and include the entire statement within quotes like this:
Looking at our example, the first part of the formula tells Excel to count only the cells that are not equal to
cappuccino. The second statement adds another condition, making sure that any cell that is equal to
espresso is also excluded from the result.
Remember that the COUNTIFS function is not case-sensitive. This means that “cappuccino” can appear in any uppercase or lowercase letters. In our table, all letters are uppercase, and yet the formula is still able to exclude them.
COUNTIF not equal to x
If you are looking to apply only one condition to your counting formula, then you can also use COUNTIF. The formula from our previous example can be written as COUNTIF not equal to cappuccino:
Since COUNTIFS only has one mandatory condition (the rest are optional), you can also write the formula as COUNTIFS not equal to cappuccino, which will return the same result:
If you have additional questions on how to count cells not equal to x or y, please post a comment or send me an email. I reply to all messages that I receive.