Excel
Word
PowerPoint
Outlook
Google Sheets
Google Docs
Google Slides

Learning Hub › Formulas › Count

Count Cells Not Equal to a Value in Excel and Google Sheets

Count 📊 All versions ✓ Google Sheets Same syntax in both apps

Purpose

Counting cells that do not equal a specific value comes up more often than you might expect. How many orders…

Counting cells that do not equal a specific value comes up more often than you might expect. How many orders are still in progress — meaning not yet Cancelled? How many products are in stock — meaning not in the Out of Stock category? How many employees have a rating that is not Needs Improvement? Every time you want to count everything except one value, this is the formula. The same COUNTIF function that counts cells equal to a value handles the not-equal case too, using the <> comparison operator inside the criteria string. It is a small syntactic twist but it opens up a whole class of inverse counting problems. If you already understand the count cells equal to a value pattern, this is the natural next step, and once you have both patterns in your head you can build the most common reports straight from the raw data without any intermediate filters or helper columns.

Syntax

✓ All versions ✓ Google Sheets = Same syntax

=COUNTIF(range, "<>value")

Arguments

Argument	Required	Description
range	Required	The cells you want COUNTIF to examine. Can be a specific range like B2:B100 or an entire column like B:B. Blank cells are counted as not equal to any specific value.
criteria	Required	The exclusion pattern, written as "<>value" inside quotes. For text use "<>Cancelled", for numbers use "<>0", for cell references use "<>"&D1. The <> operator must be inside the quotes.

How it works

The <> operator inside the criteria string tells COUNTIF to count every cell in the range that does not exactly match the value after the operator. Everything about how COUNTIF works otherwise is the same — case-insensitive text comparison, exact matching for numbers, and the whole comparison must be inside one set of quotes. The critical point that trips people up is that blank cells are counted. A blank cell is technically not equal to Cancelled, so COUNTIF includes it in the total. If you want to exclude blanks as well, you need two conditions, which means moving up to COUNTIFS and adding a second check for not-blank. The other subtlety is combining the <> operator with a cell reference — you have to concatenate the operator string with the cell using & because COUNTIF criteria are literal strings, not expressions. Once these two quirks click, the formula becomes second nature. For counting non-blank cells specifically, the count non-blank cells formula is a dedicated pattern worth knowing.

Examples

Count how many orders have a status that is not Cancelled by searching the Status column.

fx =COUNTIF(C2:C11,"<>Cancelled")

	A	B	C
1	Order ID	Status	Count Not Cancelled
2	ORD-001	Approved	=COUNTIF(C2:C11,"<>Cancelled")
3	ORD-002	Pending
4	ORD-003	Approved	8
5	ORD-004	Cancelled
6	ORD-005	Pending
7	ORD-006	Approved
8	ORD-007	Cancelled
9	ORD-008	Pending
10	ORD-009	Approved
11	ORD-010	Pending

Row 2: 8 — Two rows have the status Cancelled (ORD-004 and ORD-007). The remaining eight rows — all Approved or Pending — are counted.

The <> operator and the word Cancelled are together inside a single pair of double quotes. A common mistake is writing =COUNTIF(C2:C11,<>"Cancelled") which produces a syntax error. The entire comparison including the operator must be one quoted string.

Count how many grades are not F in a column of student grades.

fx =COUNTIF(B2:B11,"<>F")

	A	B	C
1	Student	Grade	Count Not F
2	Alice	A	=COUNTIF(B2:B11,"<>F")
3	Ben	B
4	Carla	C	8
5	Dan	F
6	Ella	B
7	Finn	A
8	Gia	F
9	Hugo	C
10	Iris	B
11	Jo	D

Row 2: 8 — Two students received an F grade (Dan and Gia). The other eight grades — A, B, C, and D values — are counted.

Text matching is case-insensitive in COUNTIF, so both F and f would be treated the same way. If you need a case-sensitive count, you must use SUMPRODUCT with EXACT instead.

Use a cell reference to exclude a value dynamically. Cell A2 contains Pending, so count rows whose status is not Pending.

fx =COUNTIF(C2:C11,"<>"&A2)

	A	B	C	D
1	Exclude	Order ID	Status	Count
2	Pending	ORD-001	Approved	=COUNTIF(C2:C11,"<>"&A2)
3		ORD-002	Pending
4		ORD-003	Approved	6
5		ORD-004	Cancelled
6		ORD-005	Pending
7		ORD-006	Approved
8		ORD-007	Cancelled
9		ORD-008	Pending
10		ORD-009	Approved
11		ORD-010	Pending

Row 2: 6 — Cell A2 contains Pending. Four rows are Pending (ORD-002, ORD-005, ORD-008, ORD-010). The remaining six rows are counted.

The & symbol concatenates the <> operator with the value from cell A2. Change A2 to Cancelled and the formula immediately returns 8, to Approved and it returns 6. This is the foundation of any interactive exclusion dashboard.

Common use cases

1. Count orders that are still active by excluding the Cancelled status

=COUNTIF(B2:B100,"<>Cancelled")

2. Count students who did not fail a course by excluding the grade F

=COUNTIF(C2:C200,"<>F")

3. Count tasks that are not yet assigned to a specific person

=COUNTIF(D2:D300,"<>John")

4. Count products that are not on clearance using a cell reference for the excluded status

=COUNTIF(E2:E500,"<>"&A1)

5. Count employees who are not in a specific department for headcount analysis

=COUNTIF(F2:F250,"<>HR")

Common errors

Syntax error or #NAME?

The <> operator is outside the quotes or the quotes are missing entirely.

Fix: The entire criteria must be one quoted string — "<>Cancelled" with both the operator and the value inside the same pair of double quotes.

Count includes blank cells unexpectedly

Blank cells are technically not equal to any specific value, so COUNTIF counts them. If your range has empty cells you did not expect to count, your total will be higher than you think.

Fix: Use COUNTIFS with a second condition to exclude blanks: =COUNTIFS(B2:B100,"<>Cancelled",B2:B100,"<>"). The second "<>" criteria excludes empty cells.

Count off by one or unexpected value

The range contains values with trailing spaces or different casing that you did not realise. For example, "Cancelled " with a trailing space is not the same as "Cancelled".

Fix: Clean the source data with TRIM to remove extra spaces, or use SUMPRODUCT for a more flexible comparison if you cannot modify the original data.

Tips and variations

Combine <> with a cell reference using concatenation

Build a flexible count by referencing a cell rather than hard-coding the excluded value. The & symbol concatenates the operator with the cell reference inside the quotes. Change the cell and every formula that points to it updates automatically.

=COUNTIF(B2:B100,"<>"&D1)

Use COUNTIFS to exclude multiple values at once

COUNTIF handles one exclusion at a time. For multiple exclusions, chain conditions in the COUNTIFS function — each range-criteria pair adds another filter that must also be true. The conditions combine with AND logic, so every excluded value is filtered out.

=COUNTIFS(B2:B100,"<>Cancelled",B2:B100,"<>Returned")

Exclude blanks while excluding a value

Use COUNTIFS with two conditions on the same range — one for the value to exclude, one for not-blank. This is the pattern I use whenever I want a clean count of active entries only.

=COUNTIFS(B2:B100,"<>Cancelled",B2:B100,"<>")

Excel vs Google Sheets

⚡

Excel vs Google Sheets

COUNTIF works identically in Excel 2003 and newer, Excel 365, and every version of Google Sheets. The <> operator is supported in both applications with the same syntax and behaviour. Formulas copy between the two apps without any changes.