Google Sheets COUNTIF: Find Cells with Partial Text

Yes. In Google Sheets, use COUNTIF with asterisks around the text you want to find: =COUNTIF(A2:A8,"*tea*") counts cells in A2:A8 that contain “tea” anywhere. The asterisks allow any text before or after the search term, and COUNTIF’s text matching is not case-sensitive.

What COUNTIF does

COUNTIF counts cells in a range that meet one condition. That condition can be an exact value, a comparison such as greater than a number, or a text pattern. For a partial text match, the pattern is the important part: an asterisk before and after a word tells Sheets to accept other characters on both sides.

Suppose A2:A8 contains “Green tea,” “Black tea,” “Tea mug,” “Coffee,” “teapot,” “Herbal Tea,” and “Tea – assorted.” With that range, the formula in the opening paragraph returns 6. Six cells contain the letters tea somewhere in their text; “Coffee” does not. The match can be at the beginning, in the middle, or at the end of a cell.

This is useful for counting products, names, notes, categories, or other entries when the full cell value varies. For example, “Herbal Tea” and “Tea – assorted” are different complete strings, but both contain the same search fragment. COUNTIF can count them together without first changing the source data.

How the asterisk wildcard works

In a COUNTIF criterion, an asterisk (*) represents zero or more characters. That means *tea* can match a cell beginning with tea, ending with tea, or containing tea between other characters. It also matches the word as part of a longer string, so “teapot” qualifies even though tea is not a separate word.

The placement of the wildcard changes what qualifies:

tea* matches text that starts with tea, such as “Tea mug” or “teapot.”
*tea matches text that ends with tea, such as “Green tea.”
*tea* matches text containing tea anywhere, including “Herbal Tea.”

These are text patterns, not word-boundary searches. If the search term is “art,” a contains pattern can also match “carton” because those letters appear consecutively inside it. If you need to count only a separate word, COUNTIF with asterisks may be too broad; consider a more specific pattern or a regular-expression approach, depending on how your text is structured.

Use a cell as the search term

A cell reference makes the count easy to change without editing the formula. Assume A2:A8 contains the seven product names above and B1 contains the search term tea. This formula returns 6:

=COUNTIF(A2:A8,"*"&B1&"*")

The ampersands join the wildcard characters to the value in B1, creating the criterion *tea*. Replace tea in B1 with another term and the result updates. This is handy for a small search box at the top of a sheet or a reusable report where the user chooses which fragment to count.

If B1 might contain accidental spaces, TRIM can remove spaces at the start and end of the search term. Assuming A2:A8 is unchanged and B1 contains “ tea ” with spaces, this version returns 6:

=COUNTIF(A2:A8,"*"&TRIM(B1)&"*")

An empty search cell deserves attention. Joining an empty B1 to the two asterisks creates a pattern that can count text entries rather than communicate a useful search result. If an empty input should show nothing, assume the same range and put this formula in the result cell; it returns a blank when B1 is empty and returns 6 when B1 is tea:

=IF(B1="","",COUNTIF(A2:A8,"*"&B1&"*"))

Question marks and literal wildcard characters

The question mark (?) is another COUNTIF wildcard. It stands for exactly one character, whereas an asterisk stands for any number of characters, including none. For instance, a pattern like te? matches three-character text such as “tea” or “ten,” but not “te” or “team.” Combining wildcards lets you describe patterns of varying lengths, though a broad pattern may match more than you intend.

A common surprise occurs when the text you are searching for actually contains an asterisk or question mark. COUNTIF treats those characters as wildcards, not ordinary punctuation. Use a tilde (~) immediately before a wildcard character to search for it literally. For example, with text in C2:C10, the criterion *~** means any characters, then a literal asterisk, then any characters. It therefore finds cells containing an actual asterisk. A tilde itself can also be escaped with another tilde.

If a search term comes from a cell and may include literal wildcard symbols, escape those symbols before building the criterion. This is more involved than the ordinary contains formula, so use it only when the search characters themselves need to be treated literally. For everyday words such as tea, the basic asterisk pattern is simpler and clearer.

Does COUNTIF distinguish uppercase and lowercase?

No. COUNTIF’s text criteria are case-insensitive in Google Sheets. Searching for tea with *tea* will match “tea,” “Tea,” and “TEA.” That behavior is usually helpful for ordinary lists, since capitalization differences do not split one category into multiple counts.

If capitalization must matter, use FIND to test each cell, because FIND is case-sensitive, and combine the results with SUMPRODUCT. Assume D2:D5 contains “Tea mug,” “tea mug,” “Herbal Tea,” and “teapot,” while B1 contains Tea. This formula returns 2: the first and third cells contain that exact capitalization.

=SUMPRODUCT(--ISNUMBER(FIND(B1,D2:D5)))

SEARCH is a related function that looks for text within other text but, unlike FIND, ignores case. For many simple counts, COUNTIF is shorter and easier to maintain. Use a case-sensitive method only when uppercase and lowercase genuinely represent different values in your data.

Common problems and how to fix them

If a formula returns zero unexpectedly, first check the range and the pattern. The search term must be joined to the wildcards correctly, and the range must include the cells you intend to count. A pattern such as tea* checks only the beginning of a cell; it will not find “Green tea.” Use a wildcard on both sides for a contains search.

Extra spaces: A space inside the search term is meaningful. Check for spaces in the source text or use TRIM on the search input when appropriate.
Different punctuation: A hyphen, apostrophe, or other character in the data can affect whether a phrase matches. Search for a shorter distinctive fragment if punctuation varies.
Numbers stored as values: A wildcard criterion is intended for text matching. If the cells contain numeric values, do not assume it searches the way their displayed formatting looks. A helper column that converts the values to text can make the intended text comparison explicit.
Too many matches: Remember that asterisks allow any number of characters, and the match can occur inside a longer word. Use a longer or more distinctive fragment, or choose a word-boundary-aware method.

Also check whether the data has been imported with unusual spaces or nonprinting characters. A visible value may not be identical to the underlying cell text. When a count seems inexplicable, test the pattern on a few known cells and inspect the source values before making the criterion broader.

COUNTIF compared with other ways to search

COUNTIF is a good choice when the goal is simply to return one number for one range and one partial-text condition. It is compact, recalculates as the data changes, and needs no helper column for ordinary text. It does not, however, return the matching rows or show where the fragment occurs.

SEARCH is useful when you want to test a particular cell or build a more customized formula around the position of a substring. FILTER is more suitable when you want to display the matching records rather than count them. Google Sheets also supports REGEXMATCH for pattern-based tests, which can be useful for more precise text rules, but regular expressions have their own syntax and are unnecessary for a basic contains check.

These functions answer different questions: COUNTIF answers “how many cells match?”, while a filtering or extraction method answers “which entries match?” Choose the least complicated method that returns the result you need. If the list is large or the rule will be reused, document what the pattern means so another editor can tell whether it counts substrings, whole words, or exact capitalization.

Frequently Asked Questions

How do I count cells that contain a word anywhere in Google Sheets?
Use =COUNTIF(A2:A100,"*word*"), replacing the range and word with your own. The asterisk on each side allows text before and after the word.

Is COUNTIF case-sensitive in Google Sheets?
No. COUNTIF ignores differences in capitalization for text criteria. If the case must match, use a case-sensitive approach such as FIND combined with SUMPRODUCT.

Can COUNTIF find part of a word?
Yes. A contains pattern can match a fragment inside a larger word, not just a separate word. Searching for “art,” for example, can match “carton.”

How do I count cells that start with specific text?
Put the asterisk after the text, as in =COUNTIF(A2:A100,"word*"). This counts cells whose text begins with word; it does not count occurrences only found later in the cell.

Why does COUNTIF match an asterisk in my search term as a wildcard?
Because * and ? have special meanings in COUNTIF criteria. Put a tilde before a wildcard character when you need to match it literally, such as ~* for an actual asterisk.

Does this formula work in Excel too?
Excel also supports COUNTIF wildcards, and the basic contains pattern is effectively the same. This explanation is for Google Sheets in a web browser; for a simple partial-text count, the wildcard idea is shared by both spreadsheet programs.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.