# How to count cells not equal to a value in Excel

Join the "not equal" operator to the value: `=COUNTIF(B2:B11,"<>"&E1)` counts every cell that isn't the value in E1, blanks included.

**Excel:** `=COUNTIF(B2:B11,"<>"&E1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Order | Status |  | Not equal to | Delivered |
| S-1001 | Delivered |  | Orders |  |
| S-1002 | Shipped |  |  |  |
| S-1003 | Delivered |  |  |  |
| S-1004 | Processing |  |  |  |
| S-1005 |  |  |  |  |
| S-1006 | Delivered |  |  |  |
| S-1007 | Returned |  |  |  |
| S-1008 | Shipped |  |  |  |
| S-1009 | Delivered |  |  |  |
| S-1010 | delivered |  |  |  |

Formula in E2: `=COUNTIF(B2:B11,"<>"&E1)`

Result: 5

## 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.

## Other ways

- `=SUMPRODUCT(--(B2:B11<>E1))`: The same count written as a comparison; easy to extend with more conditions.
- `=COUNTIFS(B2:B11,"<>"&E1,B2:B11,"<>")`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-cells-not-equal-to-a-value