Count cells not equal to

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"

COUNTIF function

The COUNTIF function counts the number of cells in a range that meet the supplied criteria:

=COUNTIF(range,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 the 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 or 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:

=COUNTIF(range,"<>"&A1)

For example, if A1 contains 100 the criteria will be "<>100" after concatenation, and COUNTIF will count cells not equal to 100:

=COUNTIF(range,"<>100")

COUNTIFS function

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

Video: How to use the COUNTIFS function