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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: COUNTIF Not Equal to Text
Excel: COUNTIF Greater Than Zero
Excel: COUNTIF with Multiple Ranges
