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:
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.
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:
where b2 is the “cell number”
Figure 2.Using a cell number
In the example above, the formula used is: