Count cells not equal to

An excel formula to count cells not equal to

Related Functions

Sponsored Link

 Count cells not equal to

If you need to count the number of cells that contain values not equal to a particular value, you can use the COUNTIF function. In the generic form of the formula (above) rng represents a range of cells, and X represents the value you don't want to count. All other values will be counted.

In the example, the active cell contains this formula:


How the formula works:

COUNTIF counts the number of cells in the range that meet criteria you supply.

In the example, we use "<>" (the logical operator for "does not equal") to count cells in the range D4:D10 that don't equal "complete". COUNTIF returns the count as a result.

COUNTIF is not case-sensitive. In this example, the word "complete" can appear in any combination of uppercase / lowercase letters and will not be counted.

If you want to use a value in another cell as part of the criteria, use the ampersand (&) character to concatenate like this:


If the value in cell a1 is "100", the criteria will be "<>100" after concatenation, and COUNTIF will count cells not equal to 100.


Sponsored Link

0 votes. 0 / 5