To count cells in Excel that do not equal a specific text value, use the formula =COUNTIF(range,"<>text"). For example, =COUNTIF(A2:A10,"<>Pending") counts every cell in A2:A10 that is not exactly the word “Pending”. This works in Excel for Microsoft 365, Excel 2021, 2019, and earlier versions on Windows and Mac.
How the “Not Equal” Operator Works in COUNTIF
The COUNTIF function takes two arguments: a range and a criterion. The criterion is a text string that defines which cells to count. To express “not equal to,” you use the <> operator inside the criteria, wrapped in double quotes. Excel treats "<>text" as a comparison that returns TRUE for any cell whose value is not exactly equal to the text.
Key details:
- The comparison is case-insensitive.
"<>pending"counts cells containing “Pending”, “PENDING”, or “pEnDiNg” as equal, so those cells are excluded. - Wildcards are not allowed with the
<>operator in the same way as with"text*". If you need to exclude cells that contain a substring, you must use SUMPRODUCT or COUNTIFS with wildcards. - Cells that are empty are counted as “not equal to” any text, because an empty cell is not equal to “Pending”. If you want to exclude blanks, you need an additional condition.
Using COUNTIFS for Multiple “Not Equal” Conditions
If you need to count cells that do not equal one text and also do not equal another, use COUNTIFS. For example, with data in A2:A20, to count cells that are neither “Pending” nor “Cancelled”:
=COUNTIFS(A2:A20,"<>Pending",A2:A20,"<>Cancelled")
This counts every cell that meets both criteria. Note that COUNTIFS also ignores blank cells only if they fail the criteria; an empty cell is not equal to “Pending” and not equal to “Cancelled”, so it would be counted. To exclude blanks, add a third criterion: A2:A20,"<>".
Handling Blank Cells and Empty Strings
A common misconception is that COUNTIF(range,"<>text") excludes blanks. It does not. In Excel, an empty cell has a value of 0 in some contexts, but for text comparisons, it is treated as an empty string. Since an empty string is not equal to “Pending”, it is counted.
To count only non-blank cells that do not equal a specific text, use:
=COUNTIFS(A2:A20,"<>Pending",A2:A20,"<>")
The second criterion "<>" means “not equal to nothing”, which effectively excludes blanks. Alternatively, use =COUNTA(A2:A20)-COUNTIF(A2:A20,"Pending") if you are certain there are no other text values you want to exclude.
Comparing with Other Functions: SUMPRODUCT and FILTER
For more complex conditions, such as excluding cells that contain a certain substring, COUNTIF is not sufficient. Instead, use SUMPRODUCT with ISNUMBER and SEARCH. For example, to count cells in A2:A10 that do not contain the word “Done” anywhere:
=SUMPRODUCT(--(ISNUMBER(SEARCH("Done",A2:A10))=FALSE))
This works by checking each cell for the substring “Done” and then counting the FALSE results. Note that SEARCH is case-insensitive; use FIND for case-sensitive matching.
If you have Excel 365 or Excel 2021, you can also use the FILTER function to return the actual cells, then count them with COUNTA:
=COUNTA(FILTER(A2:A10,A2:A10<>"Pending"))
This returns the count of cells that are not “Pending”, excluding blanks automatically because FILTER drops empty cells when the condition is false.
Common Errors and How to Fix Them
- #VALUE! error: This usually happens if the criteria syntax is incorrect, such as missing quotes or using a cell reference without concatenation. If you want to reference a cell that contains the text, use
=COUNTIF(A2:A10,"<>"&B1). - Counting blanks unintentionally: As noted, add a second criterion
"<>"to exclude empty cells. - Case sensitivity issues: COUNTIF is not case-sensitive. If you need case-sensitive comparison, use SUMPRODUCT with EXACT:
=SUMPRODUCT(--(EXACT(A2:A10,"Pending")=FALSE)). - Text with leading/trailing spaces: If your data has extra spaces, “Pending ” will not equal “Pending”. Use the TRIM function in a helper column or use SUMPRODUCT with TRIM.
Practical Examples with Sample Data
Assume you have a list of order statuses in A2:A10:
A2: Pending
A3: Shipped
A4: Pending
A5: Delivered
A6:
A7: Cancelled
A8: Pending
A9: Shipped
A10: Delivered
To count orders that are not “Pending”, use =COUNTIF(A2:A10,"<>Pending"). The result is 6, because the three “Pending” cells are excluded, and the blank cell A6 is counted (since it is not equal to “Pending”). If you want to exclude the blank, use =COUNTIFS(A2:A10,"<>Pending",A2:A10,"<>") which returns 5.
Frequently Asked Questions
How do I count cells that do not equal a number?
Use the same syntax without quotes: =COUNTIF(A2:A10,"<>5"). This counts all cells that are not equal to 5, including text and blanks. To exclude blanks, add a condition for non-blank.
Can I use a cell reference for the text in COUNTIF?
Yes. Use the ampersand to concatenate: =COUNTIF(A2:A10,"<>"&B1) where B1 contains the text you want to exclude. This is useful when the text changes.
Why does COUNTIF count cells with formulas that return an empty string?
If a formula returns "" (an empty text string), that is not the same as a truly blank cell. COUNTIF treats "" as a text value that is not equal to “Pending”, so it gets counted. To exclude such cells, use =COUNTIFS(A2:A10,"<>Pending",A2:A10,"<>""") but note that escaping quotes in Excel can be tricky. A simpler approach is to use SUMPRODUCT with LEN.
Is there a way to count cells that do not contain a specific word anywhere?
Yes, use SUMPRODUCT with ISNUMBER and SEARCH as shown above. For example, =SUMPRODUCT(--(ISNUMBER(SEARCH("Done",A2:A10))=FALSE)) counts cells that do not contain “Done” as a substring.
Does COUNTIF work with wildcards for “not equal”?
No. The <> operator does not support wildcards. If you try "<>*Done*", Excel will treat it as a literal string, not as a wildcard pattern. Use SUMPRODUCT for substring exclusions.
Alternatives and Performance Considerations
For large datasets (tens of thousands of rows), COUNTIF and COUNTIFS are fast because they are optimized. SUMPRODUCT can be slower because it performs array calculations. If you need to exclude substrings across a huge range, consider using a helper column with ISNUMBER(SEARCH()) and then COUNTIF on that column.
Another alternative is to use the FILTER function in Excel 365, which is dynamic and can spill results. However, if you only need a count, COUNTIFS is usually the most straightforward and compatible with older versions.
Remember that all formulas must start with an equals sign, use commas as argument separators, and use straight double quotes for text criteria. On Mac, the same formulas work; keyboard shortcuts differ, but the syntax is identical.
Related
- Excel Business Template for Cash Flow: How to Build One
- Excel Bullet Journal Template: Setup & Free Options
- Excel Built-In Repair Tool: What It Does and How to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Proper Case for Names: Formula and LimitsTo put names in proper case in Excel, use the PROPER function: with the original name in A2,…
- Excel Formula for BMI Calculation (Step-by-Step)The Excel formula for BMI (Body Mass Index) is =weight_kg/(height_m^2) when your data is in kilograms and meters.…
- Excel Standard Deviation Formula: Which Function to UseIn Excel for Microsoft 365, use STDEV.S to calculate standard deviation from a sample, or STDEV.P when your…
- Excel Time Sheets for Employees: Setup, Formulas and TipsYes. Excel can track employee start times, finish times, unpaid breaks, and hours worked in a reusable time…
- Insert a Horizontal Page Break in Excel the Right WayTo insert a horizontal page break in Excel for Microsoft 365 on Windows, select the row directly below…
- Excel MATCH Expressions: Syntax and ExamplesExcel’s MATCH function finds a value in a one-dimensional range and returns its position within that range—not the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.