Table of Contents
Excel formulas are powerful tools that enable users to quickly and easily manipulate data. One such formula is the COUNTIF formula, which is used to count the number of cells in a range that meet a certain criteria. In this article, we will discuss how to use the COUNTIF formula to count the number of cells in a range that are not equal to either a specific text string or a blank value. We will also discuss the advantages of using this formula and provide examples of how to use it. By the end of this article, you will have a better understanding of the importance of using this formula to quickly and accurately manipulate data.
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.