Excel COUNTIF Does Not Contain Text: Formula Guide

Formulas & functions · Updated November 22, 2026

No, Excel’s COUNTIF function does not have a built-in “does not contain” operator, but you can count cells that do not contain specific text by using the wildcard characters with a not-equal-to comparison. The standard formula is =COUNTIF(range,"<>*text*"), which counts every cell in the range that does not contain the given text anywhere. For example, =COUNTIF(A2:A10,"<>*apple*") returns the number of cells in A2:A10 that do not have the word “apple” in them. This works because the asterisks (*) act as wildcards matching any sequence of characters, and the <> operator means “not equal to.”

Why COUNTIF Needs a Workaround for “Does Not Contain”

COUNTIF’s syntax is COUNTIF(range, criteria), and its criteria are designed for exact matches, numeric comparisons, and wildcard patterns. There is no direct “does not contain” parameter, unlike functions such as FILTER which accept a NOT(ISNUMBER(SEARCH())) logic. The workaround leverages the fact that COUNTIF treats a criteria string that includes <> as “not equal to.” When you combine that with wildcard asterisks, you get “not equal to any text that has the substring between the asterisks.” This is a clever but non-obvious use of the function, and it is the standard method used by Excel trainers and forums.

How the Wildcard Not-Equal Formula Works

To count cells that do not contain a specific text string (e.g., “urgent”) in a range (e.g., B2:B100), use:

=COUNTIF(B2:B100,"<>*urgent*")

Here’s the breakdown:

  • <> means “not equal to.”
  • *urgent* matches any text that contains “urgent” anywhere, because the asterisks stand for zero or more characters.
  • Together, the criteria says “not equal to any text that has ‘urgent’ in it,” so COUNTIF counts every cell that does not match that pattern.

Important: This formula counts cells that are not text (numbers, dates, errors) as well, because they do not match the text pattern. If you want to count only text cells that do not contain the substring, you need a more complex formula, such as =SUMPRODUCT(--(ISTEXT(B2:B100)*NOT(ISNUMBER(SEARCH("urgent",B2:B100))))). But for most practical uses, the simple COUNTIF version is sufficient.

Counting Cells That Do Not Contain Any Text at All

If your goal is to count cells that are completely empty or contain only numbers (i.e., no text at all), you can use =COUNTIF(range,"<>*"). The asterisk alone matches any text, so <>* counts cells that are not text. This includes blank cells and numeric cells. To count only truly blank cells, use =COUNTBLANK(range). To count non-text cells excluding blanks, use =COUNTIF(range,"<>*")-COUNTBLANK(range).

Case Sensitivity and Exact Matches

COUNTIF is not case-sensitive. So =COUNTIF(A1:A5,"<>*apple*") will treat “Apple,” “APPLE,” and “apple” the same. If you need case-sensitive counting, you must use SUMPRODUCT with FIND instead: =SUMPRODUCT(--(ISNUMBER(FIND("apple",A1:A5))=FALSE)). Also, the wildcard method counts cells that do not contain the substring anywhere. If you want to exclude only cells that exactly equal a text string (e.g., count cells that are not exactly “apple”), use =COUNTIF(range,"<>apple") without asterisks. That counts cells that are not exactly “apple,” including cells that contain “apple pie” (since that is not an exact match).

Common Mistakes and How to Avoid Them

  • Forgetting the asterisks: Using "<>apple" only excludes exact matches, not cells that contain “apple” as part of a longer string.
  • Using the wrong operator: Some users try "*<>apple*" which is invalid. The not-equal sign must come before the wildcards.
  • Quotes around the criteria: The criteria must be in double quotes. If you reference a cell containing the text, use =COUNTIF(range,"<>*"&C1&"*").
  • Counting blanks unintentionally: The formula counts blank cells as “not containing text,” which may skew results. If you want to exclude blanks, subtract COUNTBLANK(range) or use SUMPRODUCT with ISTEXT.

Alternatives: SUMPRODUCT, FILTER, and Dynamic Arrays

For more control, especially when you need to count only text cells that do not contain a substring, use SUMPRODUCT:

=SUMPRODUCT(--(ISTEXT(A2:A10)*NOT(ISNUMBER(SEARCH("apple",A2:A10)))))

This counts cells that are text and do not contain “apple.” The SEARCH function is case-insensitive; replace with FIND for case-sensitive. In Excel 365 or 2021, you can use the FILTER function to list the cells, then count them:

=ROWS(FILTER(A2:A10,(ISTEXT(A2:A10)*NOT(ISNUMBER(SEARCH("apple",A2:A10))))))

This returns the count of rows that meet the criteria. Note that FILTER requires Excel 365 or 2021. For older versions, SUMPRODUCT is the safest bet.

Frequently Asked Questions

Can I use COUNTIF to count cells that do not contain specific text but are not blank?
Yes, use =COUNTIF(range,"<>*text*")-COUNTBLANK(range). This subtracts the blank cells from the count of cells that do not contain the text. Alternatively, use SUMPRODUCT with ISTEXT and NOT(ISNUMBER(SEARCH())) to exclude blanks and numbers.

Why does my COUNTIF formula return a larger number than expected?
It is likely counting blank cells or numeric cells because they do not match the text pattern. Check if your range includes empty cells. If so, subtract COUNTBLANK or refine the criteria with SUMPRODUCT.

Does the wildcard method work with numbers?
No, wildcards only work with text. If your range contains numbers and you use "<>*123*", it will count all numbers (since numbers are not text) and also text that does not contain “123.” To count numbers that do not contain a digit, you would need a different approach, like SUMPRODUCT with ISNUMBER and SEARCH on the text representation.

Is there a way to make COUNTIF case-sensitive?
No, COUNTIF is always case-insensitive. Use SUMPRODUCT with FIND, which is case-sensitive, as shown above. For example, =SUMPRODUCT(--(ISNUMBER(FIND("Apple",A1:A10))=FALSE)) counts cells that do not contain the exact case “Apple.”

Can I use a cell reference for the text to exclude?
Yes, concatenate the cell reference with wildcards: =COUNTIF(range,"<>*"&D1&"*"). If D1 contains “apple,” it counts cells that do not contain “apple.”

Practical Example with a Real Dataset

Suppose you have a list of product names in A2:A20, and you want to count how many products do not contain the word “sale.” The data includes:

A2: "Summer Sale"
A3: "Winter Jacket"
A4: "Sale Item"
A5: "Boots"
A6: (blank)
A7: "Sale"

Using =COUNTIF(A2:A20,"<>*sale*") returns 3 (Winter Jacket, Boots, and the blank cell). If you want to exclude blanks, use =COUNTIF(A2:A20,"<>*sale*")-COUNTBLANK(A2:A20) which gives 2. To count only text cells that do not contain “sale,” use =SUMPRODUCT(--(ISTEXT(A2:A20)*NOT(ISNUMBER(SEARCH("sale",A2:A20))))) which returns 2 as well, ignoring blanks and numbers.

Performance and Limitations

COUNTIF is fast even on large ranges, but wildcard criteria can slow down if you have hundreds of thousands of rows. SUMPRODUCT with array operations can be slower on huge datasets. For Excel 365, using FILTER with ROWS is efficient and readable. Also note that COUNTIF ignores cells with errors, but SUMPRODUCT does not—it will treat error cells as text? Actually, ISTEXT returns TRUE for error values? No, ISTEXT returns FALSE for errors. So SUMPRODUCT will exclude error cells. COUNTIF will also exclude error cells because they don’t match the text pattern. Both are safe.

Conclusion and Best Practice

For most users, the simple =COUNTIF(range,"<>*text*") is the quickest and easiest way to count cells that do not contain a specific text string. Just be aware of its behavior with blanks and numbers. If you need more precise control, switch to SUMPRODUCT or FILTER. Always test on a small sample to confirm the results match your expectation. This method works identically in Google Sheets, though the FILTER alternative uses a slightly different syntax.

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.