COUNTIF in Excel: Exclude Text from a Count

Formulas & functions · Updated November 22, 2026

To count nonblank cells while excluding any cell that contains a particular word, use =COUNTIFS(A2:A100,"<>",A2:A100,"<>*apple*"). Here, the values are in A2:A100; the formula counts cells that are not blank and do not contain “apple” anywhere.

Choose what “exclude text” means

Excel can exclude text in more than one way, so decide what you mean before choosing a criterion. You might want to exclude cells that equal a word exactly, cells that contain a word as part of a longer entry, or cells that start or end with a particular string. These are different tests: excluding the exact value “apple” does not exclude “apple pie” or “pineapple.”

For a concrete example, imagine A2:A8 contains “apple pie,” “pear,” “pineapple,” a blank cell, “pear juice,” “apple,” and “plum.” If the goal is to exclude every nonblank entry containing “apple,” only “pear,” “pear juice,” and “plum” qualify, so the result is 3. If the goal is to exclude only the exact value “apple,” five nonblank entries qualify.

  • Exact match: exclude a cell only when its whole value matches the text.
  • Contains: exclude a cell if the text appears anywhere inside it.
  • Begins or ends with: exclude entries that match at one edge of the cell.

Exclude a word wherever it appears

In the opening formula, the criterion "<>*apple*" means “not containing apple.” The less-than and greater-than symbols together mean “not equal to,” while the asterisks allow any number of characters before or after the word. That makes the test exclude “apple,” “apple pie,” and “pineapple.”

The formula uses COUNTIFS rather than COUNTIF because it applies two conditions to the same range: the cell must be nonblank, and it must not contain the excluded text. This matters when a range has empty cells. A count intended to cover only actual entries should generally state that nonblank condition explicitly rather than rely on how an exclusion criterion treats empty cells.

For the sample values in A2:A8 listed above, the formula returns 3: “pear,” “pear juice,” and “plum.” If your range is guaranteed to contain no blanks, a single COUNTIF criterion can be sufficient. COUNTIFS makes the blank-handling intention clearer and is usually the safer pattern for a list that may grow or contain gaps.

Exclude an exact match instead

To omit only cells whose entire value is exactly “apple,” use a criterion without wildcards. With the example entries in A2:A8, this formula returns 5 because it excludes the exact “apple” entry and the blank, but keeps “apple pie” and “pineapple.”

Assuming the entries are in A2:A8, this counts nonblank cells that are not exactly apple: =COUNTIFS(A2:A8,"<>",A2:A8,"<>apple").

Exact matching is useful for categories, status labels, or codes where similar-looking values should remain in the count. For instance, excluding the category “Closed” should not automatically exclude “Closed – pending review.” If that longer label also needs to be excluded, choose a contains or starts-with criterion instead.

Use wildcards for partial, beginning, or ending text

Excel criteria support wildcards, which let you describe a pattern rather than one literal value. An asterisk (*) represents any number of characters, including none. A question mark (?) represents exactly one character. These symbols can be useful for exclusions, but they also mean that a criterion may match more than the letters you intended.

  • *apple* matches apple anywhere in the cell.
  • apple* matches values that begin with apple, such as “apple pie.”
  • *apple matches values that end with apple, such as “green apple.”
  • appl? matches five-character strings beginning with appl and ending with any one character.

To exclude cells that begin with “apple,” put the asterisk after the word in a not-equal criterion. To exclude cells that end with it, put the asterisk before the word. Think through a few real examples from your data before applying a wildcard to a large range. A pattern can match unexpected labels if the same letters appear in another word or code.

Exclude more than one text value

When the rule is “exclude cells containing apple and also exclude cells containing orange,” add another criterion pair to COUNTIFS. Each criterion must be satisfied for a cell to count. With the example data in A2:A8, excluding both words anywhere still returns 3: pear, pear juice, and plum. The formula is:

Assuming the text is in A2:A8, this counts nonblank cells containing neither apple nor orange: =COUNTIFS(A2:A8,"<>",A2:A8,"<>*apple*",A2:A8,"<>*orange*").

This arrangement is suitable when every excluded term is a condition a cell must pass. It is not the same as adding two separate counts: separate counts can count the same cell more than once, especially when a cell contains both excluded terms. If the list of terms is long or changes frequently, keep the terms in a worksheet list and consider a more maintainable formula or a helper column instead of editing a long criterion formula each time.

Use a cell reference for the excluded text

A cell reference makes the exclusion easier to change. Suppose D1 contains apple and the values to check are in A2:A8. The formula below returns 3 for the sample list. Change D1 to another word and the same formula checks for that word without editing the formula itself.

Assuming the data is in A2:A8 and the excluded word is in D1, count nonblank cells that do not contain the D1 text with: =COUNTIFS(A2:A8,"<>",A2:A8,"<>*"&D1&"*").

The ampersands join the wildcard characters to the value in D1 to construct a criterion such as <>*apple*. If D1 is empty, the resulting criterion may not express a useful exclusion, so keep the control cell filled or add a separate check for that situation. Also consider trimming accidental spaces in the input: “apple” and “apple ” can behave differently in exact-match comparisons.

Case sensitivity and wildcard characters

COUNTIF and COUNTIFS criteria are not case-sensitive. An exclusion for “apple” also matches “Apple” and “APPLE.” For most ordinary labels, that is convenient. If capitalization carries meaning in your data, a standard COUNTIF criterion cannot distinguish the cases; you will need a case-sensitive approach using functions such as EXACT in a more advanced formula or a helper column.

There is another wildcard-related issue: if the text you want to exclude literally contains an asterisk or question mark, Excel may interpret that character as a pattern symbol. Use a tilde (~) immediately before a wildcard character to treat it literally. For example, ~* represents an actual asterisk rather than “any number of characters.” A literal tilde itself is represented by doubling it as ~~. This is worth checking when counting file names, product codes, or text that uses wildcard symbols.

Common problems and how to check the result

If the count seems too high or too low, first confirm that the range covers the intended rows and that the criterion describes an exact match or a partial match as intended. A typo in the excluded word, a leading or trailing space, or an unexpected blank can change the result. If entries are produced by formulas, some cells may look empty even though they contain formulas; verify those cases against the actual worksheet behavior.

  • Check a few cells that should be excluded and a few that should remain. The wildcard version should reject both “apple” and “pineapple.”
  • Check whether the range includes headers. A text header can be counted if it meets the criteria.
  • Confirm that your criteria use straight quotation marks around text in the formula bar, not typographic curly quotation marks.
  • Look for hidden spaces or inconsistent spelling. If the source data needs cleaning, fix it or use a deliberate cleanup step before counting.
  • Remember that the criterion is case-insensitive; capitalization alone will not keep a value in the count.

For a growing table, structured references can help the formula expand with the data, but the exact reference depends on the table and column names. In a small worksheet, a clearly chosen cell range is often simpler. In either case, spot-check the included and excluded entries before using the total in a report.

Frequently Asked Questions

Does COUNTIF ignore blanks when excluding text?
Do not assume that blank handling matches your intention. Add a nonblank condition with COUNTIFS, such as "<>", when you want to count populated cells only. This also makes the rule easier for someone else to understand.

How do I exclude a cell that contains a word anywhere?
Use a not-equal criterion with an asterisk on both sides of the word, such as "<>*apple*". For a populated list, pair it with a nonblank criterion in COUNTIFS.

How do I exclude only an exact word?
Use a not-equal criterion without wildcards, such as "<>apple". Add a separate nonblank condition if blank cells should not be counted.

Can COUNTIF exclude several words at once?
Yes. Use COUNTIFS with a not-containing criterion for each word. Since all COUNTIFS conditions must be met, a cell is counted only if it passes every exclusion test.

Is COUNTIF case-sensitive?
No. COUNTIF and COUNTIFS treat uppercase and lowercase text as equivalent. Use a case-sensitive method if the distinction is important to the data.

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.