In this example, the goal is to count the number of cells in column D that are not equal to a given color. The simplest way to do this is with the COUNTIF function, as explained below.
Not equal to
In Excel, the operator for not equal to 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(range,"<>10") // not equal to 10 =COUNTIF(range,"<>apple") // not equal to "apple"
This is a requirement of COUNTIF, which is in a group of eight functions that share this syntax. In example shown, we want to count cells not equal to "red" in D5:D16, so we use "<>red" for criteria. The formula in G5 is:
=COUNTIF(D5:D16,"<>red") // returns 9
In cell G6, we count all cells not equal to blue with a similar formula:
=COUNTIF(D5:D16,"<>blue") // returns 7
Note: COUNTIF is not case-sensitive. The word "red" 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 (&) operator to concatenate like this:
For example, if A1 contains 100 the criteria will be "<>100" after concatenation, and COUNTIF will count cells not equal to 100:
The COUNTIFs function is designed to handle multiple criteria, but can be used just like the COUNTIF function in this example:
=COUNTIFS(D5:D16,"<>red") // returns 9 =COUNTIFS(D5:D16,"<>blue") // returns 7