Excel COUNTIF Contains Partial Text: 3 Ways

Formulas & functions · Updated November 22, 2026

To count cells that contain part of text in Excel, use =COUNTIF(range,"*text*") — the asterisks are wildcards that match any characters before and after your text. For example, =COUNTIF(A2:A10,"*apple*") counts every cell in A2:A10 that contains the word “apple” anywhere in it. This works for exact substrings, but if you need case-insensitive matching or want to count based on a cell reference, you’ll need a small variation, covered below.

How COUNTIF Handles Wildcards

COUNTIF supports three wildcard characters: the asterisk (*) matches any sequence of characters, the question mark (?) matches any single character, and the tilde (~) escapes a wildcard so you can search for a literal asterisk or question mark. When you write "*text*", Excel looks for cells that contain “text” anywhere — at the start, middle, or end. If you want cells that start with “text”, use "text*"; to match cells that end with “text”, use "*text".

Important: COUNTIF is not case-sensitive. =COUNTIF(A2:A10,"*apple*") will count “Apple”, “APPLE”, and “apple” all the same. If you need case-sensitive counting, you must use SUMPRODUCT with ISNUMBER and FIND, as shown later.

Using a Cell Reference for the Search Text

Hardcoding the text in the formula works, but often you’ll want to reference a cell that contains the search term. To do that, concatenate the asterisks with the cell reference using the ampersand (&). With the search term in cell D1, the formula is:

=COUNTIF(A2:A10,"*"&D1&"*")

This counts every cell in A2:A10 that contains whatever text is typed in D1. If D1 is empty, COUNTIF will count all non-empty cells because "**" matches any text. To avoid that, you can wrap it in an IF: =IF(D1="",0,COUNTIF(A2:A10,"*"&D1&"*")).

Counting with SUMPRODUCT for Case-Sensitive Matches

When case matters, COUNTIF won’t work because it ignores case. Instead, use SUMPRODUCT with ISNUMBER and FIND. FIND is case-sensitive, so it returns a number (the position) only when the exact case matches. The formula for counting cells in A2:A10 that contain “apple” (lowercase only) is:

=SUMPRODUCT(--ISNUMBER(FIND("apple",A2:A10)))

The double unary (–) converts TRUE/FALSE to 1/0, and SUMPRODUCT adds them up. For a cell reference, use =SUMPRODUCT(--ISNUMBER(FIND(D1,A2:A10))). This works in all Excel versions, but it’s an array formula that processes each cell, so it’s fine for typical ranges.

Partial Match with Multiple Criteria

If you need to count cells that contain one text or another, use the plus sign in SUMPRODUCT. For example, to count cells that contain either “apple” or “banana”:

=SUMPRODUCT(--(ISNUMBER(FIND("apple",A2:A10))+ISNUMBER(FIND("banana",A2:A10))>0))

The plus adds the two arrays; if a cell contains both, the sum is 2, so the >0 ensures it counts once. Alternatively, you can add two COUNTIF formulas: =COUNTIF(A2:A10,"*apple*")+COUNTIF(A2:A10,"*banana*"), but that double-counts cells containing both. The SUMPRODUCT version is more accurate for an OR condition.

Common Pitfalls and How to Avoid Them

  • Counting blank cells accidentally: If your range contains empty cells, "*text*" will not count them because empty cells don’t match. But if you use "*" alone, it counts all non-empty cells. Be specific.
  • Literal asterisks: To count cells that contain an actual asterisk, use a tilde: =COUNTIF(A2:A10,"*~**"). The first asterisk is a wildcard, the ~* is a literal asterisk, and the last asterisk is a wildcard.
  • Case sensitivity confusion: Many users expect COUNTIF to be case-sensitive, but it isn’t. Use SUMPRODUCT/FIND if you need exact case.
  • Whole-word vs. substring: "*apple*" will match “pineapple” because it contains “apple”. If you need whole words only, you’ll need a more complex formula with ISNUMBER(SEARCH(” “&”apple”&” “,” “&A1&” “)) — but that’s beyond COUNTIF’s scope.

Alternatives: COUNTIFS, FILTER, and Dynamic Arrays

COUNTIFS works the same way but allows multiple criteria. For partial text, you can use =COUNTIFS(A2:A10,"*apple*",B2:B10,">10") to count rows where column A contains “apple” and column B is greater than 10. In Excel 365, you can also use FILTER to list the matching cells: =FILTER(A2:A10,ISNUMBER(SEARCH("apple",A2:A10))) — SEARCH is case-insensitive, FIND is case-sensitive. Then wrap it in COUNTA to get a count: =COUNTA(FILTER(A2:A10,ISNUMBER(SEARCH("apple",A2:A10)))). This is more dynamic but requires Microsoft 365 or Excel 2021.

Frequently Asked Questions

How do I count cells that contain a specific word but not as part of another word?

COUNTIF can’t do whole-word matching directly. One workaround is to add spaces around the word in your criteria: =COUNTIF(A2:A10,"* apple *") will match cells where “apple” is surrounded by spaces, but it misses “apple” at the start or end of the cell. A more robust solution uses SUMPRODUCT with TRIM and SUBSTITUTE, but it’s complex. For most cases, the wildcard method is sufficient.

Why does my COUNTIF return 0 when I know there are matches?

Check for invisible characters like spaces. If your cell contains “apple ” with a trailing space, "*apple*" still matches because the asterisk covers the space. If you’re referencing a cell that has extra spaces, use TRIM on that cell: =COUNTIF(A2:A10,"*"&TRIM(D1)&"*"). Also ensure your range is correct and doesn’t include merged cells or errors.

Can I use COUNTIF with a partial text and a date or number?

Yes, but dates and numbers are stored as numbers. If you want to count cells that contain the number 5 anywhere, use "*5*" — but note that it will match 15, 25, 50, etc. For dates, it’s better to use a separate column with TEXT function or use COUNTIFS with date ranges.

Does COUNTIF work on the web version of Excel?

Yes, COUNTIF and wildcards work in Excel for the web. SUMPRODUCT also works. The FILTER function is available in Excel for the web if you have a Microsoft 365 subscription.

Performance and Large Data Sets

COUNTIF is fast even on large ranges, but SUMPRODUCT with array operations can slow down if you have tens of thousands of rows. If performance matters, consider using a helper column with ISNUMBER(SEARCH(…)) and then COUNTIF on that column. For example, in B2: =--ISNUMBER(SEARCH("apple",A2)) gives 1 or 0, then =SUM(B2:B10000) gives the count. This is often faster than SUMPRODUCT.

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.