COUNTIFS Does Not Contain: Excel Formula and Examples

To count cells that do not contain a particular text string in Excel, use a COUNTIFS criterion with an inequality sign and wildcards: =COUNTIFS(A2:A20,"<>*apple*",A2:A20,"<>"). This counts nonblank cells in A2:A20 that do not contain “apple” anywhere; COUNTIFS has no separate “does not contain” operator.

What “Does Not Contain” Means in COUNTIFS

COUNTIFS applies one or more conditions to corresponding cells in one or more ranges, then counts the cells—or, when ranges cover rows of a table, the records—that satisfy every condition. Its criteria can test exact values, comparisons, and text patterns. “Does not contain” is a text-pattern condition: the cell must not include the specified characters as a substring.

The distinction between a substring and an exact match matters. The criterion "<>apple" excludes a cell whose entire value is exactly “apple,” but it does not exclude “green apple” or “apple juice.” To exclude all three, use asterisks around the search text: "<>*apple*". The asterisk means any number of characters, including none, before or after the word.

COUNTIFS treats text comparisons as case-insensitive. The pattern “apple” therefore also excludes “Apple,” “APPLE,” and mixed-case versions. If case matters, COUNTIFS alone is not a case-sensitive text-search function; a formula using functions such as FIND or EXACT is needed instead.

How the Wildcard Criterion Works

Excel uses two main wildcards in criteria: an asterisk (*) matches any sequence of characters, while a question mark (?) matches one character. A tilde (~) lets you treat a wildcard character as a literal character. In the criterion "<>*apple*", the angle-bracket inequality operator means “not equal to,” and the two asterisks make the comparison cover “apple” wherever it appears in the cell.

That combination is a practical way to express “does not contain,” but it is still a criteria pattern, not a natural-language search. For instance, the pattern *art* also matches “carton” and “artist,” because it looks for those letters anywhere in the text. It does not recognize word boundaries or meanings.

"*apple*" matches text containing “apple” anywhere.
"<>*apple*" excludes text containing “apple” anywhere.
"<>apple" excludes only an exact cell value of “apple.”
"*?*" is not a general “nonblank” test; use the explicit "<>" criterion to require a nonempty cell.

A Basic Example, Including Blank Cells

Suppose A2:A8 contains “Red apple,” “Apple juice,” “green pear,” “Pineapple,” a blank cell, “PEAR,” and “apple pie.” If the goal is to count nonblank entries that do not contain “apple,” use both a not-containing criterion and a not-blank criterion. The first removes matching text; the second makes the treatment of empty cells explicit.

With the example entries in A2:A8, this formula returns 2: =COUNTIFS(A2:A8,"<>*apple*",A2:A8,"<>"). The two counted values are “green pear” and “PEAR.” “Pineapple” is excluded because it contains the letters “apple,” and the blank is excluded by the second condition. Adding "<>" is a useful safeguard whenever blanks should not contribute to the answer.

COUNTIFS combines its criteria with AND logic. Each condition must be true for a cell to be counted. In this example, a cell must both not contain “apple” and not be blank. Repeating the same range is valid when applying separate conditions to each cell in that range.

Using a Cell as the Search Term

Putting the search term in a worksheet cell makes the formula easier to update. With descriptions in A2:A20 and the text to exclude in D2, use this formula: =COUNTIFS(A2:A20,"<>*"&D2&"*",A2:A20,"<>"). If D2 contains “apple,” it applies the same pattern as the fixed-text example. Changing D2 changes the search term without editing the formula.

The ampersands join the wildcard characters to the value in D2. The criteria string is assembled as “not equal to, any characters, the contents of D2, any characters.” If D2 is blank, the resulting pattern can behave unexpectedly for the intended task, so it is sensible to keep the search-term cell filled or add a separate check for it.

Search terms that themselves contain * or ? need extra care, because Excel interprets those characters as wildcards. To search for a literal wildcard, precede it with a tilde in the criteria pattern. For example, a pattern that excludes text containing a literal asterisk is "<>*~**": the first and last asterisks are wildcards, and ~* represents a literal asterisk. When building patterns from arbitrary user-entered text, escape wildcard characters in that input before joining it to the criteria.

Adding Other Conditions

A common use is counting records that meet a category condition while excluding a phrase from a description. COUNTIFS can test different columns in the same formula. With status values in B2:B100 and descriptions in A2:A100, this counts rows marked “Open” whose description does not contain “overdue,” while excluding blank descriptions: =COUNTIFS(B2:B100,"Open",A2:A100,"<>*overdue*",A2:A100,"<>"). The result is the number of rows where all three conditions are satisfied.

Every criteria range in a COUNTIFS formula must have the same dimensions. For example, A2:A100 and B2:B100 align row by row, but pairing A2:A100 with B2:B99 is not a valid way to compare records. When extending a formula, check that each range starts and ends on the same rows. If the formula is copied down, consider absolute references such as $A$2:$A$100 when the ranges should stay fixed.

For a numeric condition alongside the text exclusion, the same AND rule applies. With descriptions in A2:A100 and amounts in C2:C100, this counts nonblank descriptions that do not contain “refund” and have an amount greater than 100: =COUNTIFS(A2:A100,"<>*refund*",A2:A100,"<>",C2:C100,">100"). The amount criterion is numeric; the wildcard criterion tests the text pattern.

What Counts as Text, Numbers, and Errors

Wildcard patterns are intended for text matching. A numeric cell does not contain a text substring in the same way a text description does, so a formula designed to screen descriptions should ideally refer to a range that actually holds descriptions. If the range mixes text, numbers, and blanks, test a representative sample rather than assuming every value will behave like a text entry.

Errors in a criteria range can also complicate results. If cells may contain errors such as #N/A, clean or handle those values deliberately and check the workbook’s actual data. COUNTIFS is not a general-purpose error-cleaning function. Likewise, a cell that appears empty may contain a formula returning an empty string; it can behave differently from a truly empty cell in some counting situations. If the distinction matters, test the formula against the workbook’s real data and decide what “blank” should mean for the task.

COUNTIFS supports up to 127 range-and-criteria pairs. Most everyday formulas need only a few, but long formulas can become difficult to audit. Clear helper columns or a more structured analysis may be easier to maintain when many exclusions and conditions are involved.

Common Mistakes and How to Fix Them

Using only an inequality criterion: "<>apple" excludes an exact match, not phrases containing “apple.” Add the wildcards: "<>*apple*".
Forgetting the blank condition: If blank cells must not count, add the same range with criterion "<>". This states the requirement directly.
Putting the wildcards inside the search-cell value: When the term is in D2, join it to the asterisks with ampersands, as in "<>*"&D2&"*".
Expecting case sensitivity: COUNTIFS text criteria do not distinguish uppercase from lowercase. Choose a different formula approach if capitalization must affect the count.
Using ranges with different sizes: Keep all COUNTIFS ranges aligned and equal in dimensions so each condition evaluates the intended row.
Expecting a whole-word match: A wildcard search finds a sequence anywhere. It does not distinguish a standalone word from the same letters inside a longer word.

When checking a surprising result, first compare the included and excluded rows manually, then check blanks, spelling, spaces, and whether the search term contains wildcard characters. A trailing space in a source value or a term can change what matches. If data comes from another system, extra spaces may be hard to see; cleaning the source or using a helper column can make the counting rule more dependable.

COUNTIFS Compared with Other Options

Use COUNTIF when there is one range and one condition; use COUNTIFS when several conditions must all be true. For example, the single-condition version of the basic exclusion is =COUNTIF(A2:A20,"<>*apple*"), while COUNTIFS is useful for adding the explicit nonblank test or conditions in other columns. The two functions share the same general criteria and wildcard conventions.

For more complex logic—such as case-sensitive matching, custom word-boundary rules, or several alternative exclusions—a helper column or a formula using other text functions may be clearer than a long COUNTIFS expression. COUNTIFS is strongest when the rule can be expressed as a set of straightforward criteria. It is available in current desktop Excel, including Microsoft 365 for Windows, as well as Excel for Mac and Excel for the web; the formula syntax shown here is the same in those versions.

Frequently Asked Questions

What is the COUNTIFS formula for does not contain?
Use a criterion such as "<>*text*", replacing text with the string to exclude. To rule out blanks too, add the same range again with criterion "<>".

Does COUNTIFS “does not contain” count blank cells?
Do not rely on an implicit blank treatment if the distinction matters. Add a separate nonblank criterion, for example A2:A20,"<>", to make sure empty cells are not included.

Is COUNTIFS case-sensitive?
No. Its text criteria are case-insensitive, so “apple” also matches “Apple” and “APPLE.” Use a different formula design when uppercase and lowercase must be treated differently.

How do I exclude a word in one column but count only a particular status?
Add a condition for the status range and a not-containing condition for the description range. COUNTIFS applies AND logic, so each row must meet both conditions to be counted.

Why does “does not contain” exclude Pineapple?
Because "<>*apple*" searches for the letters “apple” anywhere in a cell. It does not check whether “apple” is a separate word. Use a different approach if you need whole-word matching.

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.