Table of Contents
The COUNTIF function in Excel is a useful tool for counting the number of cells that meet a specific criteria. This function can also be used to count cells that are not equal to a certain text or are blank. To use this function, you would first need to select the range of cells that you want to count from. Then, you would input the criteria in the COUNTIF formula, using the “not equal to” operator (<>) and the specific text or a blank space. This will return the number of cells in the selected range that do not match the specified criteria. This function can be helpful in quickly identifying and counting cells that do not contain a certain value or are empty.
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:
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
=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: