To count cells that do not contain a specific text string in Excel, use the COUNTIF function with the “not equal to” operator and wildcards: =COUNTIF(range,"<>*text*"). For example, =COUNTIF(A2:A10,"<>*pending*") counts every cell in A2:A10 that does not contain the word “pending” anywhere in its text. This works in Excel for Microsoft 365, Excel 2021, 2019, and earlier versions on Windows and Mac.
How COUNTIF Handles Text Matching
COUNTIF is a conditional counting function that evaluates each cell against a criterion. The criterion is a text string that can include comparison operators and wildcards. When you use <> (not equal to) with text, Excel treats the text literally unless you add wildcards. Without wildcards, =COUNTIF(A2:A10,"<>pending") counts cells that are not exactly the word “pending” — meaning a cell containing “Pending approval” would be counted because it is not exactly equal. To exclude any cell that contains the substring “pending”, you must wrap the text in asterisks: "<>*pending*".
Wildcards in Excel COUNTIF:
*matches any sequence of characters (including zero).?matches any single character.~escapes a wildcard, so~*matches a literal asterisk.
So "<>*apple*" counts cells that do not have “apple” anywhere, while "<>apple" counts cells that are not exactly the word “apple”. Choose based on whether you need a partial match or an exact match.
Using the Not-Equal Criterion with Cell References
If you want to compare against a value stored in another cell, you can concatenate the operator with the cell reference. For example, with the excluded text in D1, use:
=COUNTIF(A2:A100,"<>"&D1)
But note: if D1 contains “pending”, this counts cells that are not exactly equal to “pending”. To exclude any cell that contains the text in D1 as a substring, you need to add wildcards around the reference:
=COUNTIF(A2:A100,"<>*"&D1&"*")
This works because the ampersand (&) joins the text pieces into a single criterion string. The formula assumes your data is in A2:A100 and the excluded text is in D1. For a sample where A2 is “Approved”, A3 is “Pending review”, and D1 is “pending”, the second formula returns 1 (only A2) because A3 contains “pending” as a substring.
Counting Cells That Do Not Match a List of Text Values
Sometimes you need to count cells that do not match any of several text strings. The simplest approach is to subtract the matches from the total count. For example, to count cells in A2:A10 that are not “red”, “green”, or “blue”:
=COUNTA(A2:A10)-SUM(COUNTIF(A2:A10,{"red","green","blue"}))
This uses COUNTA to count all non-empty cells, then subtracts the sum of COUNTIF for each color. The array constant {"red","green","blue"} works in all Excel versions. If you have a list of excluded values in a range (say D2:D4), use:
=COUNTA(A2:A10)-SUMPRODUCT(COUNTIF(A2:A10,D2:D4))
SUMPRODUCT forces the array calculation without needing Ctrl+Shift+Enter in older versions. This counts cells that do not exactly match any value in D2:D4. For partial matches against a list, you would need a more complex array formula or a helper column.
Common Problems and Solutions
Problem: The formula counts blank cells. COUNTIF with "<>*text*" counts blank cells because a blank cell is not equal to the text. To exclude blanks, use =COUNTIFS(range,"<>*text*",range,"<>""") — note the double quotes inside to represent a blank. For example:
=COUNTIFS(A2:A100,"<>*pending*",A2:A100,"<>""")
This counts non-blank cells that do not contain “pending”.
Problem: Case sensitivity. COUNTIF is not case-sensitive. "<>*PENDING*" and "<>*pending*" behave the same. If you need case-sensitive exclusion, use SUMPRODUCT with EXACT:
=SUMPRODUCT(--(NOT(ISNUMBER(SEARCH("pending",A2:A100)))))
But SEARCH is also not case-sensitive. For case-sensitive, use FIND:
=SUMPRODUCT(--(ISERROR(FIND("pending",A2:A100))))
This counts cells where FIND returns an error (i.e., the text is not found). Note that this counts blank cells as well; to exclude blanks, add (A2:A100<>"").
Problem: The text contains a wildcard character. If you want to exclude cells containing an asterisk, use "<>*~**" — the tilde escapes the first asterisk, and the surrounding asterisks act as wildcards. For a question mark, use "<>*~?*".
Alternatives: COUNTIFS, SUMPRODUCT, and FILTER
COUNTIFS extends COUNTIF to multiple criteria. For example, to count cells in column A that do not contain “pending” and are in column B with a value greater than 10:
=COUNTIFS(A2:A100,"<>*pending*",B2:B100,">10")
SUMPRODUCT is more flexible for array-based logic. The formula =SUMPRODUCT(--(ISNUMBER(SEARCH("pending",A2:A100)))) counts cells that do contain “pending”; subtract from COUNTA to get the opposite. For a direct not-contains count, use ISERROR as shown above.
In Excel 365, the FILTER function can return the actual cells that do not match, and then you can count them with ROWS:
=ROWS(FILTER(A2:A100,(A2:A100<>"")*(ISERROR(SEARCH("pending",A2:A100)))))
This returns the number of non-blank cells that do not contain “pending”. FILTER is available in Excel 365 and Excel 2021. On Mac, the same formulas work; the only difference is keyboard shortcuts for entering array formulas in older versions (Ctrl+Shift+Enter on Windows, Cmd+Shift+Enter on Mac).
Frequently Asked Questions
How do I count cells that do not contain a specific word in Excel?
Use =COUNTIF(range,"<>*word*"). Replace range with your cell range and word with the text you want to exclude. For example, =COUNTIF(B2:B50,"<>*urgent*") counts all cells in B2:B50 that do not have “urgent” anywhere. Remember this counts blank cells too; add a second condition if you want to ignore blanks.
Why does COUNTIF not work with text containing a hyphen or slash?
It does work. Hyphens and slashes are treated as normal characters. However, if your text contains a wildcard character like * or ?, you need to escape it with a tilde (~). For example, to exclude cells containing “A*B”, use "<>*A~*B*".
Can I use COUNTIF to count cells that do not equal a number?
Yes. For numeric criteria, use =COUNTIF(range,"<>5") to count cells not equal to 5. This works for numbers without wildcards. For example, =COUNTIF(C2:C10,"<>0") counts non-zero cells. Note that blank cells are counted as not equal to 5, so use =COUNTIFS(range,"<>5",range,"<>""") to exclude blanks.
Does COUNTIF ignore case?
Yes, COUNTIF is not case-sensitive. “PENDING” and “pending” are treated the same. If you need case-sensitive matching, use SUMPRODUCT with FIND, as shown above. For example, =SUMPRODUCT(--(ISERROR(FIND("Pending",A2:A100)))) counts cells that do not contain “Pending” with that exact capitalization.
How do I count cells that do not contain any of multiple text strings?
Use the subtraction method: =COUNTA(range)-SUMPRODUCT(COUNTIF(range,list_range)) where list_range contains the excluded exact texts. For partial matches, you would need an array formula like =SUM(--(MMULT(--ISNUMBER(SEARCH(transpose(list),range)),ROW(INDIRECT("1:"&COUNTA(list)))^0)=0)), which is complex. A simpler approach is to use a helper column with a formula like =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))=0,1,0) and then sum that column.
What is the difference between COUNTIF and COUNTIFS?
COUNTIF counts cells that meet a single criterion. COUNTIFS counts cells that meet multiple criteria across multiple ranges. For example, =COUNTIF(A2:A10,"<>*done*") uses one range and one criterion. =COUNTIFS(A2:A10,"<>*done*",B2:B10,">5") requires both conditions to be true. COUNTIFS is available in all Excel versions from 2007 onward.
Practical Example with a Sample Dataset
Suppose you have a task list in column A (A2:A10) with values: “Approved”, “Pending review”, “Done”, “In progress”, “Pending approval”, “Completed”, “N/A”, “Pending”, “Closed”. To count how many tasks are not pending in any form, use:
=COUNTIF(A2:A10,"<>*pending*")
This returns 6 because three cells contain “pending” (A3, A5, A8) and the other six do not. If you also want to exclude blanks, and you had a blank cell, use =COUNTIFS(A2:A10,"<>*pending*",A2:A10,"<>""") which would return 6 if no blanks exist, or fewer if there were blanks.
For a case-sensitive count of cells that do not contain “done” (exact capitalization), use:
=SUMPRODUCT(--(ISERROR(FIND("done",A2:A10))))
This counts cells that do not have “done” anywhere, including blanks. To exclude blanks, add *(A2:A10<>"") inside the SUMPRODUCT.
Tips for Large Datasets and Performance
COUNTIF is fast even on large ranges. SUMPRODUCT with array operations can be slower on hundreds of thousands of rows. If you need to count not-contains for a very large dataset, consider using a helper column with the ISERROR(SEARCH(…)) formula and then sum that column. For example, in B2 enter =--ISERROR(SEARCH("pending",A2)) and copy down, then use =SUM(B2:B100000). This avoids volatile array calculations and is easier to debug.
In Excel for the web, all these formulas work the same. There is no difference in syntax, but array formulas in older versions might require Ctrl+Shift+Enter on Windows and Cmd+Shift+Enter on Mac. In Excel 365 and 2021, dynamic arrays handle them automatically.
Related
- Splunk Split Command: How to Use It
- Python split() and join() Explained with Examples
- Snowflake SPLIT_PART: Syntax, Examples, and Edge Cases
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel FILTER Function With Multiple CriteriaYes. In Microsoft Excel for Microsoft 365, use the FILTER function with multiplication between conditions for AND logic,…
- Concatenate Text and Formula in Excel: ExamplesTo concatenate text and a formula in Excel, join them with the ampersand (&) operator. With the words…
- Excel SUM OFFSET: Create Flexible, Expanding TotalsIn Excel, combine SUM and OFFSET to total a range whose size or position can change: for example,…
- Excel LEFT: How to Return Text Before a Specific WordTo return everything before a particular word in an Excel cell, use =TEXTBEFORE(A2,B2) in Microsoft 365, where A2…
- How to Align Dates in Excel: Format and Match ThemTo align dates visually, select the date cells and choose an alignment option in Home > Alignment, such…
- SUMIF Excel Cell Value: Criteria, Examples & FixesTo make SUMIF use a value stored in a cell, put the cell reference in its criteria argument.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.