How to count cells not equal to a value in Excel
Formula (Excel and Google Sheets)
=COUNTIF(B2:B11,"<>"&E1)Join the "not equal" operator to the value: =COUNTIF(B2:B11,"<>"&E1) counts every cell that isn't the value in E1, blanks included.
- Result on the example
- 5 in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
"<>" means "not equal to", and joining it to E1 builds the criteria "<>Delivered". COUNTIF then counts the shipped, processing and returned orders, plus S-1005, whose status is blank, because a blank cell is not "Delivered" either.
The comparison ignores case, so "delivered" in lowercase counts as delivered and is left out.
=COUNTIF(B2:B11,"<>"&E1)Result5
B2:B1110 rows × 1 col: "Delivered", "Shipped", "Delivered", "Processing", …A range: 10 cells in one column.
E1"Delivered"The value in E1.
"<>"&E1"<>Delivered"The & operator joins as text.
COUNTIF(B2:B11,"<>"&E1)5COUNTIF: Counts cells that meet one condition.- range
B2:B1110 rows × 1 col: "Delivered", "Shipped", "Delivered", "Processing", … - criteria
"<>"&E1"<>Delivered"
- range
In Google Sheets
The same formula works in Google Sheets: =COUNTIF(B2:B11,"<>"&E1). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(B2:B11<>E1)) | 5 | The same count written as a comparison; easy to extend with more conditions. |
| =COUNTIFS(B2:B11,"<>"&E1,B2:B11,"<>") | 4Different result | Adds a second condition that skips blank cells. Use it when blanks are rows that haven't been filled in yet. Leaves out S-1005, whose status is blank. |
Common mistakes
"<>E1"inside the quotes compares with the text "E1". Keep the cell reference outside:"<>"&E1.- Blank cells count as "not equal". If that inflates your number, add
,B2:B11,"<>"as a second condition.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count cells not equal to a value
E2, count the orders in B2:B11 whose status is not the value in E1.Questions
How do I count cells not equal to several values?
Add one "<>" condition per value in COUNTIFS: =COUNTIFS(B2:B11,"<>Delivered",B2:B11,"<>Returned").
How do I count cells not equal to a number?
The same way: =COUNTIF(C2:C11,"<>0"). Blanks are counted, so see the page on counting non-zero values.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.