Excel formula: Count cells not equal to

Generic formula

=COUNTIF(rng,"<>X")

Summary

To count the number of cells that contain values not equal to a particular value, you can use the COUNTIF function. In the generic form of the formula (above) rng 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, H5 contains this formula:

=COUNTIF(D5:D11,"<>Complete")

Explanation

In Excel, the operator for not equal 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(A1:A10,"<>10") // count cells not equal to 10
=COUNTIF(A1:A10,"<>apple") // count cells not equal to "apple"

In example shown, we want to count cells not equal to "complete", so we use "<>complete" for criteria like this:

=COUNTIF(D5:D11,"<>Complete") // count not equal to "complete"

Note: COUNTIF is not case-sensitive. The word "complete" 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 (&) character to concatenate like this:

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

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

=COUNTIF(rng,"<>100")

Author

Dave Bruns

Related formulas

Count cells not equal to x or y

The COUNTIFS function counts the number of cells in a range that meet one or more supplied criteria. All conditions must pass in order for a cell to be counted. In the example shown, there is a list of colors in column B in a named range called rng...

Count cells not equal to many things

First, a little context. Normally, if you have just a couple things you don't want to count, you can use COUNTIFS like this: = COUNTIFS ( range , "<>apple" , range , "<>orange" ) But this doesn't scale very well if...

Count cells equal to

The COUNTIF function is fully automatic — it counts the number of cells in a range that match the supplied criteria. For range, we use D5:D11, which contains colors. For criteria, we simply use "red". COUNTIF returns the count of values in D5:D11...

Count cells that contain specific text

The COUNTIF function counts cells in a range that meet criteria. For example, to count the number of cells in a range that contain "a" you can use: = COUNTIF ( range , "a" ) // exact match However, note this is an exact match. In order to...

Related functions

Excel COUNTIF Function

COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical...

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.