Excel Formula: COUNTIF Not Equal to Text or Blank


You can use the following formula in Excel to count the number of cells in a range that are not equal to specific text or a blank:

=COUNTIFS(A2:A12, "<>Mavs", A2:A12, "<>")

This particular formula counts the number of cells in the range A2:A12 that are not equal to “Mavs” or a blank.

Note: The <> operator stands for “not equal” in Excel.

The following example shows how to use this formula in practice.

Example: COUNTIF Not Equal to Text or Blank in Excel

Suppose we have the following dataset that contains information about various basketball players:

Suppose we would like to count the total number of cells in the Team column that are not equal to “Mavs” or a blank value.

We can type the following formula into cell D2 to do so:

=COUNTIFS(A2:A12, "<>Mavs", A2:A12, "<>")

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

Excel COUNTIF not equal to text or blank

From the output we can see that there are 6 cells not equal to “Mavs” or a blank cell in the Team column.

We can verify this is correct by manually identifying each of the 6 cells that are not equal to “Mavs” or a blank cell:

How This Formula Works

Recall the formula that we used to count the number of cells that were not equal to “Mavs” or a blank value:

=COUNTIFS(A2:A12, "<>Mavs", A2:A12, "<>")

This formula uses the COUNTIFS function, which counts the number of cells in a range that meet multiple criteria.

The first criteria that we specify is that the cell should not be equal to Mavs – “<>Mavs”

The second criteria that we specify is that the cell should not be blank – “<>”

The COUNTIFS function then counts all cells in the range A2:A12 that meet both of these criteria.

In our particular example, we saw that 6 cells met both of these criteria.

Additional Resources

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

Excel: Use COUNTIF with Multiple Ranges
Excel: Use COUNTIF with Multiple Criteria in Same Column
Excel: COUNTIF Greater Than But Less Than Some Number

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *