Excel guide · 3 min read
COUNTIF not equal to in Excel
=COUNTIF(B2:B10,"<>Closed") to count cells that are not equal to Closed. "<>" means "not equal to" and goes inside the quotes. It also counts blank cells, so use =COUNTIFS(B2:B10,"<>Closed",B2:B10,"<>") when empty rows should not count.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Ticket | Status | Agent | Subject | Exclude | |
| 2 | T-3301 | Closed | Dana | Refund not received | Closed | |
| 3 | T-3302 | Open | Luis | Login problem | ||
| 4 | T-3303 | Pending | Dana | Refund request | ||
| 5 | T-3304 | Wrong size delivered | ||||
| 6 | T-3305 | Closed | Mei | Change address | ||
| 7 | T-3306 | Spam | You won a prize | |||
| 8 | T-3307 | Open | Mei | Partial REFUND | ||
| 9 | T-3308 | closed | Luis | Invoice copy | ||
| 10 | T-3309 | Closed | Dana | Login problem |
Download this sheet as an Excel practice file (.xlsx)
The basic not-equal criteria
Put the operator and the value together in one text string: "<>Closed". COUNTIF is not case-sensitive, so "closed" in row 9 counts as Closed and is excluded.
Six of the nine tickets are counted: Open, Pending, Spam, the second Open, the blank status in row 5, and "Closed " in row 10, which has a trailing space and so is not exactly Closed.
Result: 6
6 tickets are not Closed, including one with no status and one whose "Closed " has a trailing space.
Not equal to a cell
To make the excluded value easy to change, keep it in a cell and join it to the operator with &. Writing "<>F2" inside one pair of quotes would compare against the text F2 instead.
If F2 is empty, the criteria becomes just "<>", which means "not empty". The formula then counts every non-blank status, which can surprise you on a dashboard with an unfilled filter cell.
Result: 6
6, the same as typing "<>Closed", but now you can type Open or Spam in F2 to exclude something else.
Not equal and not blank
Blank cells are "not equal" to anything, so they count. When a blank means "no ticket yet" or an unused row, add a second condition on the same range with "<>" alone. COUNTIFS counts a row only when every condition is true.
This is the version most people actually want for "open work" counts.
Result: 5
5: the blank status in row 5 is no longer counted.
Not equal to several values
To exclude Closed and Spam, repeat the range with a second "<>" condition. People often reach for OR here, but "not Closed and not Spam" is an AND, which is exactly what COUNTIFS does. Adding a "<>" condition for blanks keeps empty rows out too.
For a long exclusion list, subtract instead: =COUNTA(B2:B10)-COUNTIF(B2:B10,"Closed")-COUNTIF(B2:B10,"Spam").
Result: 4
4: Open, Pending, Open and the "Closed " with a trailing space.
Does not contain a word
Wildcards work with "<>": the asterisk stands for any characters, so "<>*refund*" counts cells that do not contain refund anywhere. It is not case-sensitive, so "Partial REFUND" is excluded along with the other two refund tickets.
The trailing-space problem from row 10 has a wildcard fix too: "<>Closed*" excludes anything that starts with Closed. Be careful, because it also excludes values like "Closed - duplicate". Cleaning the data with TRIM is the safer long-term fix.
Result: 6
6 subjects do not mention a refund. =COUNTIF(D2:D10,"*refund*") counts the other 3.
Not equal in IF and other formulas
Inside IF, SUMPRODUCT or any comparison, <> is written without quotes around the operator: B3<>"Closed". Only the criteria functions (COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS) take the operator inside a text string.
To count with SUMPRODUCT while ignoring stray spaces, compare the trimmed value: =SUMPRODUCT((TRIM(B2:B10)<>"Closed")*(B2:B10<>"")) gives 4 here, because "Closed " now matches Closed and is excluded, while Spam still counts.
Result: Needs action
Needs action, because ticket T-3302 is Open.
Common mistakes
The count is higher than expected
"<>value" counts blank cells. Add a second condition: COUNTIFS(range,"<>value",range,"<>").
Writing "<>"F2 or "<>F2"
Join the operator to the cell with &: "<>"&F2. Inside the quotes, F2 is just text.
Using OR logic to exclude two values
Not A and not B is an AND. Use COUNTIFS with two "<>" conditions on the same range.
Values that look equal are still counted
There is a hidden space, like "Closed ". Clean the column with TRIM or use the "<>Closed*" wildcard.
FAQ
What is the "not equal to" operator in Excel?
It is <> (less than followed by greater than). In COUNTIF and SUMIF it goes inside quotes, "<>Closed". In normal formulas it stands alone: =A2<>"Closed".
Does COUNTIF "<>" count blank cells?
"<>" on its own counts only non-blank cells. "<>Closed" counts blank cells, because an empty cell is not equal to Closed.
How do I count cells that do not contain specific text?
Use wildcards: =COUNTIF(range,"<>*text*"). It also counts blanks, so add ,range,"<>" in COUNTIFS to leave them out.
Is COUNTIF not equal case-sensitive?
No. "<>Closed" excludes Closed, closed and CLOSED alike. For a case-sensitive count, use SUMPRODUCT with EXACT in Excel.
Functions used
COUNTIFCounts cells that matchCOUNTIFSCounts with several conditionsCOUNTACounts non-empty cellsIFPicks a result from a testSUMPRODUCTMultiply then addTRIMRemoves extra spaces
Related guides
- COUNTIF vs COUNTIFS: what is the difference?
- COUNTIFS with multiple criteria in Excel
- COUNTIF not working: causes and fixes
- How to count cells with text in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to find duplicates in Excel
- How to count blank cells in Excel
Updated