How to count cells not equal to x or y using COUNTIFS

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 Cappuccino or Espresso.

  1. Type =COUNTIFS(
  2. Select the range or type the range name. In my example, I’ve used a named range called beverage.
  3. Insert the first condition as "<>cappuccino". This tells Excel that we do not want to count any cell that has the text cappuccino.
  4. 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.
  5. Insert the second condition, which in our case is "<>espresso".
  6. 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")

Count cells not equal to x or y

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: "<>cappuccino".

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:

=COUNTIF(E23:E42, "<>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:

=COUNTIFS(E23:E42, "<>cappuccino")

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.

About me

My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.

If you'd like to get in touch, you can contact me on LinkedIn.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

>