COUNTIF Not Equal To: Count Cells That Don't Match

To use COUNTIF for "not equal to", put <> in front of the value inside the quotes: =COUNTIF(C2:C9,"<>Shipped") counts every cell that is anything other than Shipped. The same idea works with a cell reference, with numbers and with several values to exclude. This guide is for anyone who needs to count "everything except" in a report or a job test, and it points out the blank-cell trap that catches most people.

How to write a COUNTIF not equal to formula#

Write the not-equal sign, <>, directly before the value, and put both inside one pair of double quotes. The pattern is:

=COUNTIF(range,"<>value")

All the examples use this order list in A1:D9. Order O-105 has no status yet, so C6 is empty:

To count the orders that are not from the North region:

=COUNTIF(B2:B9,"<>North")

The result is 5: two South, two East and one West. COUNTIF is not case-sensitive, so "north" in a cell would also be left out. The COUNTIF function guide has the full syntax.

Why <> means not equal to#

In Excel, <> is the not equal to operator: "less than or greater than", which means "different from". It is one of six comparison operators:

The != sign from programming languages does not work in Excel, and neither does =!. The guide to Excel comparison operators shows each one in formulas, and the lesson on TRUE, FALSE and comparisons lets you try them.

The blank-cell trap#

A blank cell is "not equal to Shipped", so COUNTIF counts it. On the order list:

=COUNTIF(C2:C9,"<>Shipped")

The result is 4: two Pending, one Cancelled and the empty status of O-105. That is often what you want, because an order with no status is certainly not shipped. But if you only want to count orders with some other status, leave the blanks out with COUNTIFS and a second "not blank" condition:

=COUNTIFS(C2:C9,"<>Shipped",C2:C9,"<>")

The result is now 3. On its own, "<>" means "not equal to nothing", which is how Excel says "not blank". The guide to COUNTIF not blank covers that criteria in more detail.

How to count cells not equal to a cell value#

To compare against a value in another cell, keep <> in quotes and join the cell with the & sign. If F1 holds the word Pending:

=COUNTIF(C2:C9,"<>"&F1)

The result is 6: four Shipped, one Cancelled and the blank. Change F1 to Shipped and the answer updates to 4.

Two versions of this formula look right but fail:

• =COUNTIF(C2:C9,"<>F1") counts cells that are not the text "F1". Every cell qualifies, so you get 8.
• =COUNTIF(C2:C9,<>F1) is not a valid formula. Excel shows an error message before you can even press Enter.

If you fill the formula down a summary table, lock the range with $: =COUNTIF($C$2:$C$9,"<>"&F1).

How to exclude two or more values#

COUNTIF takes only one condition, so to exclude two values use COUNTIFS with the same range twice. To count orders that are neither Shipped nor Cancelled:

=COUNTIFS(C2:C9,"<>Shipped",C2:C9,"<>Cancelled")

The result is 3: the two Pending orders and the blank one. COUNTIFS uses AND logic, so a cell is counted only when it passes every condition. Add a third pair to exclude a third value.

You can also mix "not equal to" with other conditions. North orders that are not Shipped:

=COUNTIFS(B2:B9,"North",C2:C9,"<>Shipped")

That returns 1 (O-104, Cancelled). The COUNTIFS function guide and the COUNTIFS and SUMIFS lesson cover multi-condition counts step by step.

A common slip is to write two COUNTIFs and subtract or add them. =COUNTIF(C2:C9,"<>Shipped")+COUNTIF(C2:C9,"<>Cancelled") returns 4 + 7 = 11, more than the number of orders, because each COUNTIF counts the other's rows.

Not equal to a number, zero or partial text#

Numbers work the same way, with or without a cell:

The last one uses the * wildcard, which stands for any characters. "<>*pend*" excludes every cell that contains "pend" anywhere. Like the other examples, it still counts the blank status.

COUNTIF ignores capitals, so "<>shipped" also leaves out "Shipped" and "SHIPPED". If case matters, for example with product codes, use EXACT inside SUMPRODUCT instead:

=SUMPRODUCT(--NOT(EXACT(C2:C9,"Shipped")))

EXACT returns TRUE only for a perfect match, including capitals. NOT flips each result, and the double minus turns TRUE and FALSE into 1 and 0 for SUMPRODUCT to add. On this data the result is 4, the same as before, because every status is typed the same way.

The same <> criteria work in SUMIF and SUMIFS when you want a total instead of a count. See SUMIF not equal to and SUMIFS not equal to.

Check your result: total minus matches#

A quick way to check any "not equal to" count is to take the total number of rows and subtract the matches:

=ROWS(C2:C9)-COUNTIF(C2:C9,"Shipped")

This gives 8 − 4 = 4, the same as "<>Shipped". If your two answers differ, look for extra spaces in the data: "Shipped " with a trailing space is not equal to "Shipped", so it is counted when you don't expect it. The COUNTIF guide lists the other reasons counts go wrong.

Practise it#

Try the exercise on this page: it counts the orders that are not from the North region. Then practise COUNTIF in a live spreadsheet, where you type each criteria and get instant feedback. When you're ready for two conditions, move on to the COUNTIFS and SUMIFS lesson.