Table of Contents
Excel Formulas are an integral part of any Excel spreadsheet. Excel Formulas make it possible to quickly and accurately analyze data. One of the most powerful and useful Excel formulas is the COUNTIF Not Equal to X or Y formula. This formula allows users to quickly and accurately count the number of cells within a range that do not equal a specified value or values. This formula is especially useful when trying to quickly determine the number of cells that do not meet certain criteria.
You can use the following formula in Excel to only count values in a range that are not equal to two specific values:
=COUNTIFS(B2:B13,"<>Guard",B2:B13,"<>Center")
This particular formula counts the number of cells in the range B2:B13 that are not equal to Guard or Center.
The following example shows how to use this formula in practice.
Example: COUNTIF Not Equal to X or Y in Excel
Suppose we have the following dataset in Excel that shows the positions of various basketball players:
Suppose we would like to count the number of cells in the Position column that are not equal to Guard or Center.
We can type the following formula into cell D2 to do so:
=COUNTIFS(B2:B13,"<>Guard",B2:B13,"<>Center")
The following screenshot shows how to use this formula in practice:
We can see that there are a total of 5 cells in the Position column that do not have a value of Guard or Center.
We can confirm this is correct by manually identifying each of these players:
How This Formula Works
Recall the formula that we used to count the number of cells in the Position column that are not equal to Guard or Center:
=COUNTIFS(B2:B13,"<>Guard",B2:B13,"<>Center")
This formula uses the COUNTIFS function, which counts the number of cells in a range that meet multiple conditions.
Our first condition tells Excel to look for cells that do not ( <> ) have a value equal to “Guard” in the range B2:B13.
Our second condition tells Excel to look for cells that do not ( <> ) have a value equal to “Center” in the range B2:B13.
The end result is that we’re able to count the number of cells that do not have a value equal to “Guard” or “Center in the range B2:B13.