Count cells not equal to

The Formula

=COUNTIF(rng,"<>Y")

Example and Explanation 

The COUNTIF function returns the number of cells containing values not equal to the specified value Y. In the formula above, rng is used to denote the range of cells under consideration, and Y denotes the value or cell you don’t want to be counted. All other values apart from the specified value Y are counted.

Figure 1.Counting cells not equal to a specified value

In the example shown in the screenshot, the formula used is:

=COUNTIF(E5:E11,"<>Cleared")

How this formula works

The COUNTIF function identifies the range specified and counts all the number of cell in the range after the specified value or cell has been omitted.

The logical operator “<>” which means “does not equal” is used in the example above to return the number of cells between E5 and E11 (E5:E11) is not equal to “cleared”. COUNTIF returns the number of cells counted as the result.

Notes

The COUNTIF function is not case-sensitive. For instance, the word “cleared” can appear in uppercase or lowercase letters and will still be omitted.

To set the value to be omitted as cell number, the ampersand (&) character should be used  to concatenate like this:

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

where b2 is the “cell number”

Figure 2.Using a cell number

In the example above, the formula used is:

=COUNTIF(D5:D11,"<>"&D7)

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar