Excel COUNTIF Does Not Contain Specific Text?

Formulas & functions · Updated November 22, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.