Count cells not equal to

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 shown, H4 contains this formula:

=COUNTIF(D4:D10,"<>Complete")

How this formula works

In Excel, the operator for not equal is "<>". For example:

=A1<>10 // A1 is not equal to 10
=A1<>"apple" // A1 is not equal to "apple"

The COUNTIF function counts the number of cells in a range that meet supplied criteria. To use the not equal to operator in COUNTIF, it must be enclosed in double quotes like this:

=COUNTIF(A1:A10,"<>10") // count cells not equal to 10
=COUNTIF(A1:A10,"<>apple") // count cells not equal to "apple"

In example shown, we want to count cells not equal to "complete", so we use "<>complete"  for criteria like this:

=COUNTIF(D4:D10,"<>Complete") // count not equal to "complete"

Note: COUNTIF is not case-sensitive. The word "complete" can appear in any combination of uppercase / lowercase letters.

Not equal to another cell

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

=COUNTIF(rng,"<>"&A1)

For example, if the value in cell a1 is "100", the criteria will be "<>100" after concatenation, and COUNTIF will count cells not equal to 100:

=COUNTIF(rng,"<>100")