Go Back

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)

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

i need to find cells combination from b2 to b940 that equal to a specified value
Solved by S. J. in 14 mins
Hi, How do i count the number of values greater than a specified value in a changing specified range of cells?
Solved by T. E. in 28 mins
I need help with a formula. I need to figure out if a number is greater than specified number but less than specified number to equal a specified value. Please help.
Solved by F. A. in 22 mins

Leave a Comment

avatar