How do I create an Excel formula to count cells that are not equal to X or Y?

By /

This process involves using a formula in Microsoft Excel to count the number of cells in a specific range that do not contain the values X or Y. This can be achieved by using the “COUNTIF” function and specifying the criteria as “not equal to X or Y”. This formula is useful for analyzing data and identifying the number of cells that do not match certain criteria. By following a few simple steps, one can easily create this formula and obtain the desired count result.

Excel Formula: COUNTIF Not Equal to X or Y

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:

Excel COUNTIF not X or Y

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:

x

Related terms:

  1. How do I use the COUNTIF function in Excel to count cells that are not equal to a specific text or blank?
  2. Excel Formula: COUNTIF Not Equal to Text or Blank
  3. Excel Formula: SUMIFS Not Equal to Multiple Criteria
  4. Excel Formula: COUNTIF Not Equal to X or Y
  5. How can I use conditional formatting in Google Sheets to highlight cells that are greater than or equal to a certain value?
  6. How can I use the COUNTIF function in Excel to count cells that do not contain a specific value?
  7. Excel: Apply Formula Only to Filtered Cells
  8. How do you check if multiple cells are equal?
  9. How to apply conditional formatting if two cells are not equal
  10. How to Calculate Sum If Cells Not Equal to Value?