The direct answer: use =COUNTIF(range,"<>text") to count cells in a range that do not equal a specific text value. For example, =COUNTIF(A2:A20,"<>Pending") counts every cell in A2:A20 that is not exactly the word “Pending”. The <> operator means “not equal to” in Excel, and when combined with COUNTIF it excludes matching text (and, as explained below, also excludes blank cells).
How COUNTIF Handles the Not-Equal Operator
COUNTIF’s syntax is COUNTIF(range, criteria). The criteria argument accepts comparison operators like >, <, >=, and <>. When you write "<>text", Excel interprets it as "not equal to the literal text". The operator must be enclosed in double quotes along with the text, because Excel treats the entire criteria as a string. If the text itself contains a wildcard character (* or ?), you must escape it with a tilde (~) to match the literal character. For instance, to count cells not equal to "A*", use =COUNTIF(A2:A20,"<>A~*").
One important nuance: COUNTIF with <>text does not count blank cells. If a cell is truly empty, it is not counted as "not equal to text" because empty cells are ignored by COUNTIF. If you need to include blanks in the count (i.e., count all non-matching cells including blanks), you would use =COUNTA(range)-COUNTIF(range,"text"), but that also counts blanks. To count non-blank cells that are not equal to text, use =COUNTIFS(range,"<>text",range,"<>").
Practical Examples with Real Data
Suppose you have a task list in column A (A2:A20) with statuses like "Done", "In Progress", "Pending", and some blank cells. You want to count how many tasks are not "Pending". The formula =COUNTIF(A2:A20,"<>Pending") returns the number of cells that contain any text or number other than "Pending". If A2:A20 has 10 "Done", 3 "In Progress", 2 "Pending", and 5 blanks, the result is 13 (10+3). Blanks are not counted.
To count cells that are not equal to a text value and also not blank, use =COUNTIFS(A2:A20,"<>Pending",A2:A20,"<>"). In the same example, this returns 13 as well, because blanks are already excluded. If you wanted to include blanks in the "not equal" count, you would need a different approach: =ROWS(A2:A20)-COUNTIF(A2:A20,"Pending"), which counts every cell in the range (including blanks) minus the exact matches. That returns 18-2=16.
Using Cell References and Other Criteria
Instead of hardcoding text, you can reference a cell that contains the text to exclude. For example, with the value "Pending" in D1, use =COUNTIF(A2:A20,"<>"&D1). The ampersand (&) concatenates the operator with the cell value. This makes the formula dynamic—change D1 and the count updates.
You can also combine not-equal with other conditions using COUNTIFS. For instance, to count rows where column A is not "Pending" and column B is "High", use =COUNTIFS(A2:A20,"<>Pending",B2:B20,"High"). The same operator works in SUMIF, AVERAGEIF, and their plural forms. For example, to sum sales in B2:B20 where the region in A2:A20 is not "East", use =SUMIF(A2:A20,"<>East",B2:B20).
Common Pitfalls and How to Avoid Them
- Blank cells are ignored: As noted, COUNTIF does not count empty cells when using
<>. If you need to count blanks as "not equal", use=COUNTBLANK(range)+COUNTIF(range,"<>text")—but that double-counts if you're not careful. Better to use=ROWS(range)-COUNTIF(range,"text"). - Leading/trailing spaces: If your data has extra spaces, "Pending" with a space will not match the criteria. Use
=COUNTIF(range,"<>"&TRIM(D1))or clean data with TRIM. - Case sensitivity: COUNTIF is not case-sensitive. "PENDING" and "pending" both match "Pending". If you need case-sensitive exclusion, use SUMPRODUCT with EXACT:
=SUMPRODUCT(--(NOT(EXACT(A2:A20,"Pending")))). This counts all cells (including blanks) that are not exactly "Pending" in a case-sensitive way. - Wildcards: If your text contains
*or?, they are treated as wildcards unless escaped. For example, to exclude cells that start with "A", you might think"<>A*"excludes all cells starting with A, but it actually excludes only cells that are exactly "A*"? No—the*is a wildcard, so"<>A*"means "not equal to any text starting with A", which is likely not what you want. To exclude cells that are exactly the literal "A*", use"<>A~*".
Comparing Alternatives: COUNTIFS, SUMPRODUCT, and FILTER
While COUNTIF is the simplest, other functions offer more flexibility. COUNTIFS allows multiple criteria and can exclude blanks with "< >" (no space) as a second condition. SUMPRODUCT can handle array logic, including case-sensitive comparisons and counting based on other columns. For example, =SUMPRODUCT(--(A2:A20<>"Pending")) counts non-blank cells that are not "Pending" (blanks are treated as not equal, so they are counted). To exclude blanks, add --(A2:A20<>")—but that's redundant because A2:A20<>"Pending" is TRUE for blanks. So SUMPRODUCT counts blanks as not equal. If you need to exclude blanks, use =SUMPRODUCT((A2:A20<>"Pending")*(A2:A20<>"")).
In Excel 365, the FILTER function can return the actual non-matching cells, and you can count them with =ROWS(FILTER(A2:A20,A2:A20<>"Pending")). This includes blanks, because blank cells are not equal to "Pending". To exclude blanks, add a condition: =ROWS(FILTER(A2:A20,(A2:A20<>"Pending")*(A2:A20<>""))). FILTER is more versatile but requires Microsoft 365 or Excel 2021.
Frequently Asked Questions
Why does COUNTIF with "<>text" not count blank cells?
Because COUNTIF only evaluates cells that contain a value. Blank cells are ignored entirely, so they are not considered "not equal". If you need to count blanks as part of the non-matching group, use =ROWS(range)-COUNTIF(range,"text") or SUMPRODUCT.
How do I count cells that are not equal to a number?
Use the same operator with a number: =COUNTIF(range,"<>5") counts cells that are not equal to 5. Note that text cells are counted as not equal to 5, and blanks are ignored.
Can I use COUNTIF to count cells that are not equal to one of several texts?
Yes, but you need multiple COUNTIFs: =COUNTIF(range,"<>Text1")-COUNTIF(range,"Text2") is not correct. Better to use =SUMPRODUCT(--(A2:A20<>"Text1")*(A2:A20<>"Text2")) or =ROWS(range)-COUNTIFS(range,"Text1")-COUNTIFS(range,"Text2") if you want to exclude both. The SUMPRODUCT approach counts blanks as not equal, so adjust if needed.
Why is my formula returning 0 even though I have non-matching cells?
Check for leading/trailing spaces, or that the text in the criteria exactly matches the cell content. Also ensure the range is correct and that you're not using a full-column reference that includes headers. If the text contains a wildcard character, escape it with a tilde.
Does COUNTIF work with text that contains a question mark?
Yes, but the question mark is a wildcard. To match a literal question mark, use ~?. For example, to count cells not equal to "What?", use =COUNTIF(range,"<>What~?").
Summary and Best Practices
To count cells not equal to a text value, the core formula is =COUNTIF(range,"<>text"). Remember that blanks are excluded, so if your data has blanks and you want them counted, switch to =ROWS(range)-COUNTIF(range,"text") or use SUMPRODUCT. Always test with a small dataset to verify the count matches your expectation. If you need case-sensitive exclusion or multiple exclusions, use SUMPRODUCT or FILTER. For dynamic criteria, reference a cell with the ampersand operator. These techniques apply to Excel 365, Excel 2021, and older versions, though FILTER requires Excel 365 or 2021.
Related
- Excel and Access Training: What You Actually Need
- Convert KMZ to Excel: 4 Ways That Actually Work
- DepEd Class Attendance Sheet Template Excel: Free Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel String Reverse: Functions, Formulas, and LimitsExcel does not have a built-in worksheet function named REVERSE. With the text in A2, enter =IF(A2="","",TEXTJOIN("",TRUE,MID(A2,SEQUENCE(LEN(A2),1,LEN(A2),-1),1))) in…
- COUNTIF Formula for Dates in ExcelIf your dates are in A2:A100 and the date to check is in D2, use =COUNTIF(A2:A100,D2) to count…
- Index Match Across Two Sheets in Excel: A Practical GuideYes. In Excel, INDEX and MATCH can look up a value on one worksheet and return a related…
- Using IF in Excel for Text: Compare, Match, and Return ResultsYes. In Excel, IF can test whether a cell contains particular text and return one result when it…
- Excel Formula Bot Free? Free Plans, Limits, and Safe UseYes—many Excel formula bots offer free access, but it is usually limited by usage, features, or account requirements.…
- Long Invoice Template Excel: Layout and FormulasA long invoice template in Excel is a worksheet designed to handle many billable lines while keeping the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.