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:
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
