Exceljet

Quick, clean, and to the point

Count cells not equal to

Excel formula: Count cells not equal to
Generic formula 
=COUNTIF(range,"<>x")
Summary 

To count the number of cells that are not equal to a given value, you can use the COUNTIF function. In the generic form of the formula (above) range represents a range of cells, and x represents the value you don't want to count. All other values will be counted. In the example shown, G5 contains this formula:

=COUNTIF(D5:D16,"<>red")

COUNTIF returns 9, since there are nine cells in D5:D16 that are not equal to "red".

Explanation 

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 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 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:

=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

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.