Yes, you can use COUNTIF to count cells that do not contain specific text by using the wildcard operator <> (not equal to) combined with the asterisk wildcard, like this: =COUNTIF(range,"<>*text*"). This formula counts every cell in the range that does not have the specified text anywhere within it. The key is that the not-equal-to operator (<>) works with wildcards in COUNTIF, so you can exclude cells that contain a substring. Below, we break down how this works, show examples, and cover common pitfalls.
How COUNTIF Handles Text and Wildcards
COUNTIF is a core Excel function that counts cells meeting a single condition. Its syntax is =COUNTIF(range, criteria). For text, the criteria can include wildcards:
*(asterisk) matches any sequence of characters (including zero characters).?(question mark) matches any single character.~(tilde) escapes the next character, so you can match a literal asterisk or question mark.
When you combine the not-equal-to operator with wildcards, Excel interprets the criteria as “not equal to any string that contains the pattern.” For example, "<>*apple*" means “not equal to any string that has ‘apple’ anywhere in it.” This is exactly what you need to count cells that do not contain a specific text.
The Basic Formula: Counting Cells That Do Not Contain Text
Assume you have a list of product names in cells A2:A10. To count how many of those cells do NOT contain the word “apple”, you would use:
=COUNTIF(A2:A10,"<>*apple*")
This formula counts every cell in A2:A10 that does not have “apple” as a substring. For example, if A2:A10 contains: “apple pie”, “banana”, “apple cider”, “grape”, “pineapple”, “orange”, “apple sauce”, “peach”, “plum”, then the formula returns 6 (banana, grape, orange, peach, plum, and note: “pineapple” contains “apple” as a substring, so it is excluded).
Case Sensitivity and Exact Matches
COUNTIF is not case-sensitive by default. So "<>*apple*" will also exclude cells containing “Apple” or “APPLE”. If you need case-sensitive counting, you must use SUMPRODUCT with the ISNUMBER and FIND functions. For example, to count cells that do NOT contain “apple” (case-sensitive), use:
=SUMPRODUCT(--(ISNUMBER(FIND("apple",A2:A10))=FALSE))
This works because FIND is case-sensitive and returns a number if the text is found; wrapping it in ISNUMBER gives TRUE for found, then we negate with =FALSE to count not-found cells.
Also note that "<>*apple*" treats cells with empty strings as “not containing apple”, so they are counted. If you want to exclude blanks, add a condition: =COUNTIFS(A2:A10,"<>",A2:A10,"<>*apple*").
Using COUNTIFS for Multiple Conditions
If you need to count cells that do not contain one text but also meet another condition, use COUNTIFS. For instance, to count cells in A2:A10 that do not contain “apple” AND have a corresponding value greater than 10 in B2:B10, use:
=COUNTIFS(A2:A10,"<>*apple*",B2:B10,">10")
This is a common need when filtering data based on multiple criteria.
Common Problems and How to Fix Them
Here are frequent issues users run into:
- Counting cells that are not exactly a specific text: If you want to count cells that are not exactly “apple” (but may contain it), use
"<>apple"without asterisks. This counts cells that are not exactly the string “apple”. - Counting cells that do not start with a text: Use
"<>apple*"to exclude cells that start with “apple”. - Counting cells that do not end with a text: Use
"<>*apple". - Wildcard characters in the text itself: If your text contains an asterisk or question mark, you need to escape it with a tilde. For example, to count cells that do not contain the literal string “*”, use
"<>~*". - Blanks are counted: As mentioned, the basic formula counts empty cells. To exclude blanks, add a condition with COUNTIFS as shown above, or use
=COUNTA(range)-COUNTIF(range,"*apple*")but that also counts non-text values.
Alternatives: SUMPRODUCT and Dynamic Arrays
While COUNTIF is straightforward, there are alternatives for more flexibility:
- SUMPRODUCT with ISNUMBER and SEARCH: For case-insensitive counting, you can use
=SUMPRODUCT(--(ISNUMBER(SEARCH("apple",A2:A10))=FALSE)). SEARCH is case-insensitive, so this is similar to COUNTIF but allows array operations. - FILTER and COUNTA (Excel 365): If you have Excel 365, you can use
=COUNTA(FILTER(A2:A10,ISNUMBER(SEARCH("apple",A2:A10))=FALSE)). This returns the count of cells that do not contain “apple”. - COUNTIF with helper column: Sometimes it’s easier to create a helper column that flags whether a cell contains the text, then count the flags. For example, in B2 enter
=IF(ISNUMBER(SEARCH("apple",A2)),1,0)and drag down, then use=COUNTIF(B2:B10,0).
Frequently Asked Questions
How do I count cells that do not contain any of several specific texts? Use multiple COUNTIFS with OR logic is tricky. A simpler approach is to count all cells and subtract the ones that contain any of the texts. For example, to count cells that do not contain “apple” or “banana”, use =COUNTA(A2:A10)-SUMPRODUCT(--(ISNUMBER(SEARCH("apple",A2:A10))+ISNUMBER(SEARCH("banana",A2:A10))>0)). This works because SEARCH returns an array of TRUE/FALSE for each cell, and we add them to see if any is true.
Why does my formula count blanks? COUNTIF with "<>*text*" counts any cell that is not equal to a string containing the text. An empty cell is not equal to that, so it is counted. If you want to ignore blanks, add a second condition: =COUNTIFS(range,"<>",range,"<>*text*").
Can I use this with numbers? Yes, but be careful. If your range contains numbers, the wildcard criteria will treat them as text. For example, "<>*123*" will count cells that do not contain the digit sequence “123” as text. If you want to count numbers that are not equal to a specific number, use "<>123" without asterisks.
Does this work in Google Sheets? Yes, the same formula syntax works in Google Sheets. However, note that Google Sheets uses ~ for escaping as well, and the functions are the same. The main difference is that Google Sheets may handle wildcards slightly differently with certain locale settings, but in general, =COUNTIF(range,"<>*text*") works.
How do I count cells that do not contain text at all (i.e., numbers or blanks)? Use =COUNTIF(range,"<>*") which counts cells that do not contain any text. This will count numbers, dates, and blanks. To exclude blanks, add =COUNTIFS(range,"<>",range,"<>*").
Practical Example with Data
Let’s walk through a real example. Suppose column A has the following values in A1:A6: “apple”, “banana”, “cherry”, “apple pie”, “grape”, “” (empty). To count cells that do not contain “apple”, use =COUNTIF(A1:A6,"<>*apple*"). This returns 4 because “apple” and “apple pie” are excluded, and the empty cell is counted. If you want to exclude the empty cell, use =COUNTIFS(A1:A6,"<>",A1:A6,"<>*apple*") which returns 3 (banana, cherry, grape).
If you need to count cells that do not contain “apple” and also have a length greater than 5, you can use =SUMPRODUCT((ISNUMBER(SEARCH("apple",A1:A6))=FALSE)*(LEN(A1:A6)>5)). In this example, “banana” (6) and “cherry” (6) and “apple pie” (9) but “apple pie” contains apple, so only banana and cherry count, giving 2.
Performance and Large Data Sets
COUNTIF is generally fast even on large ranges, but using wildcard searches with leading asterisks (like "*text*") can be slower because Excel has to scan every character. If you have hundreds of thousands of rows, consider using a helper column with ISNUMBER(SEARCH()) and then count with COUNTIF, or use Power Query to transform data. For most everyday use, COUNTIF is perfectly adequate.
Conclusion
Counting cells that do not contain specific text is a common task, and Excel’s COUNTIF with the not-equal-to operator and wildcards handles it elegantly. Remember to adjust for case sensitivity, blanks, and exact matches as needed. With the examples and alternatives above, you should be able to solve most variations of this problem.
Related
- Snowflake SPLIT Syntax: Arrays, Examples, and Tips
- Snowflake Split String Into Rows: SQL Examples and Tips
- Snowflake SPLIT: How to Split Strings by Delimiter
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Average Filtered Column in Excel: Formula and TipsTo average only the visible rows in a filtered Excel column, use =SUBTOTAL(1,B2:B100), replacing B2:B100 with your data…
- Accumulated Depreciation Formula in ExcelFor straight-line depreciation, accumulated depreciation is the depreciation recorded to date: annual depreciation multiplied by the number of…
- Excel Day Name from Date: Formulas, Formats, and FixesTo show the day name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains…
- Excel Consolidate Text Data: Formulas and Power QueryTo consolidate text in Excel, join cells with TEXTJOIN or combine rows from multiple tables with Power Query.…
- Excel Difference Between Dates: Formulas and ExamplesTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- Clear a Filter in an Excel Table: Steps and OptionsTo clear a filter in an Excel table, open the drop-down arrow in the filtered column’s header and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.