Excel: How to Count Cells Not Equal to Value


You can use the following formulas to count the number of cells in Excel not equal to some value:

Method 1: Count Cells Not Equal to Value

=COUNTIF(A1:A100, "<>value")

This formula counts the number of cells in the range A1:A100 that are not equal to value.

Method 2: Count Cells Not Equal to Several Values

=COUNTIFS(A1:A100, "<>value1", A1:A100, "<>value2", A1:A100, "<>value3")

This formula counts the number of cells in the range A1:A100 that are not equal to value1 or value2 or value3.

The following examples show how to use each method in practice.

Example 1: Count Cells Not Equal to Value

Suppose we have the following data in Excel:

We can use the following formula to count the number of cells in the Team column that are not equal to “A”:

=COUNTIF(A2:A13, "<>A")

The following screenshot shows how to use this formula in practice:

Excel countif not equal to value

This tells us that there are 10 cells in the Team column not equal to “A.”

Example 2: Count Cells Not Equal to Several Values

Once again suppose we have the following data in Excel:

We can use the following formula to count the number of cells in the Team column that are not equal to “A” or “B”:

=COUNTIFS(A2:A13, "<>A", A2:A13, "<>B")

The following screenshot shows how to use this formula in practice:

This tells us that there are 4 cells in the Team column not equal to “A” or “B.”

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Count Filtered Rows in Excel
How to Use COUNTIF with OR in Excel
How to Use COUNTIF From Another Sheet in Excel

Leave a Reply

Your email address will not be published.