Count cells not equal to

=COUNTIF(rng,"<>X")
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, H5 contains this formula:
=COUNTIF(D5:D11,"<>Complete")
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:
In example shown, we want to count cells not equal to "complete", so we use "<>complete" for criteria like this:
=COUNTIF(D5:D11,"<>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")
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.