Google Sheets COUNTIF Partial Match: Examples and Tips

Yes. In Google Sheets, use asterisks as wildcards in COUNTIF to count cells containing a piece of text: with the values to check in A2:A100, the formula is =COUNTIF(A2:A100,"*tea*"). The asterisks let any number of characters appear before or after “tea,” so the formula counts matches anywhere in each cell.

Partial-match counting is useful for searching product names, email addresses, notes, codes, and other text that may not be identical from row to row. The details matter: COUNTIF is not case-sensitive, wildcard characters have specific meanings, and a value that looks like text may actually be stored as a number. The examples below show how to choose and troubleshoot the right pattern.

How COUNTIF partial matching works

COUNTIF takes a range and one criterion: =COUNTIF(range, criterion). In Google Sheets, a criterion can include wildcard characters. An asterisk (*) stands for zero or more characters, while a question mark (?) stands for exactly one character. Put wildcards inside the quoted criterion to make the match partial.

Suppose A2:A7 contains “Green tea,” “black tea,” “teapot,” “Tea bags,” “coffee,” and “tea.” With those values in A2:A7, this formula returns 5 because five cells contain the letters “tea” in sequence: =COUNTIF(A2:A7,"*tea*"). The match is not restricted to a word boundary, so “teapot” qualifies even though “tea” is only the start of a longer word.

COUNTIF checks each cell against the criterion and returns one total. It does not return the matching rows or extract the matching text. If you need to see or work with the matching records, a filter or a different function may be more appropriate.

Match text anywhere, at the beginning, or at the end

The position of the asterisk controls where the text can appear. Use wildcards on both sides for a substring anywhere, on the right for a beginning match, and on the left for an ending match.

Anywhere: with text in A2:A100, =COUNTIF(A2:A100,"*north*") counts cells containing “north” anywhere.
Beginning: with text in A2:A100, =COUNTIF(A2:A100,"North*") counts values that start with “North,” such as “North Road.”
End: with text in A2:A100, =COUNTIF(A2:A100,"*Road") counts values that end with “Road,” such as “North Road.”

These patterns also help distinguish a substring search from a prefix or suffix search. For instance, “North*” will not count “Old North Road,” because the value does not begin with “North.” “*North*” will count it.

Use a cell as the search term

Rather than typing the search term directly into a formula, you can keep it in a cell and join it to wildcard characters with the ampersand (&). This makes the search easy to change without editing the formula. With values in A2:A100 and the term to find in D2, use =COUNTIF(A2:A100,"*"&D2&"*"). If D2 contains “tea,” the formula counts cells containing “tea” anywhere.

This approach is convenient for a dashboard or a small search box, but it has an important caveat: asterisks and question marks typed into D2 are interpreted as wildcards, not necessarily as literal characters. If a user enters a blank search term, the joined criterion becomes two asterisks, which can count text cells broadly rather than meaningfully searching for a word. To avoid a misleading result, you can leave the result blank when D2 is empty. With data in A2:A100 and a search term in D2, use =IF(D2="","",COUNTIF(A2:A100,"*"&D2&"*")).

Count more than one condition

When a partial match must also meet another requirement, COUNTIFS can apply criteria to multiple ranges. Each range must correspond to the same rows. For example, suppose product names are in A2:A100 and regions are in B2:B100. To count rows where the product contains “tea” and the region is exactly “West,” use =COUNTIFS(A2:A100,"*tea*",B2:B100,"West"). The result includes only rows satisfying both conditions.

COUNTIFS is an AND test: every criterion must be met for a row to count. It does not mean “match tea or coffee.” For an either/or count, you can add separate COUNTIF results. With product names in A2:A100, use =COUNTIF(A2:A100,"*tea*")+COUNTIF(A2:A100,"*coffee*") to add the two counts. If a cell contains both terms, it is counted twice in that sum, so this method is suitable only when that outcome is intended.

Wildcards, literal symbols, and spaces

Wildcards are useful, but sometimes the character you want to find is itself an asterisk or question mark. In Google Sheets criteria, place a tilde (~) before a wildcard character to treat it literally. To count cells containing an actual asterisk, with text in A2:A20, use =COUNTIF(A2:A20,"*~**"). The first and last asterisks allow other text around the literal asterisk in the middle.

The same escape rule applies to a literal question mark: with text in A2:A20, =COUNTIF(A2:A20,"*~?*") counts cells containing a question mark. To search for a literal tilde, escape it with another tilde; with text in A2:A20, use =COUNTIF(A2:A20,"*~~*").

Spaces and punctuation are part of the text. Searching for “tea” will match “iced-tea” and “tea bags,” but searching for “tea ” includes a trailing space in the criterion and can produce different results. If an apparently matching value is missed, inspect the cell for leading or trailing spaces, nonbreaking spaces, or punctuation that was not included in the search term.

Case sensitivity and other matching options

COUNTIF partial matches are case-insensitive. The criterion “*tea*” counts “Tea,” “TEA,” and “tea” alike. That is usually helpful for ordinary text searches, but it is not appropriate if capitalization carries meaning, such as distinguishing a case-sensitive identifier.

For a case-sensitive substring count, use FIND inside SUMPRODUCT. With text in A2:A100, this formula counts cells containing the exact-case text “Apple”: =SUMPRODUCT(--ISNUMBER(FIND("Apple",A2:A100))). FIND is case-sensitive, while SEARCH is not. For example, if the range contains “Apple pie” and “apple tart,” the formula shown counts the first cell but not the second. SUMPRODUCT totals the TRUE/FALSE tests across the range.

For a straightforward partial search, COUNTIF is usually easier to read and maintain than a more elaborate formula. Use a case-sensitive formula only when the distinction is genuinely important, and test it against a few known values before relying on the total.

Common problems and how to fix them

The result is zero: check that the range contains the expected data and that the criterion has wildcards on both sides when the text may be in the middle. Confirm the spelling and look for extra spaces or punctuation.
The count is unexpectedly high: remember that asterisk means any number of characters, including none. A short search term can occur inside many unrelated words; use a more distinctive phrase or a beginning/end pattern if that better represents the task.
Only some similar-looking values match: a cell may contain a number, date, or other underlying value rather than text. Display formatting does not necessarily change the stored value. For codes with leading zeros or mixed formats, make the data consistent as text before relying on a text wildcard search.
The formula shows an error: check that the quotes are straight double quotes, the parentheses are balanced, and the range is valid. Google Sheets normally uses commas between function arguments in English-language locales, but some spreadsheet locales use semicolons.
The formula changes when copied: relative references such as A2:A100 adjust when moved. If the range should stay fixed, use absolute references such as $A$2:$A$100.

For larger sheets, prefer a range that covers the actual data rather than an unnecessarily broad range. Also check that every range in COUNTIFS begins and ends on corresponding rows; mismatched ranges can make the formula invalid or produce results that do not represent the intended records.

COUNTIF compared with filters and search tools

Choose COUNTIF when the desired output is a count. It updates as the data or search term changes and keeps the result in a cell. A filter is better when you need to inspect, edit, or copy the matching rows. In Google Sheets, you can use the filter controls on a range to display rows that meet text conditions, rather than building a count formula.

Find is useful for locating a term in the sheet, but it is an interactive search rather than a reusable summary calculation. A wildcard count is also different from an exact match: =COUNTIF(A2:A100,"tea") counts cells equal to “tea,” while =COUNTIF(A2:A100,"*tea*") counts cells with “tea” anywhere in their contents. Excel also supports COUNTIF wildcard criteria, but this article’s menu and function examples are for Google Sheets in a web browser; the Sheets interface and some available functions are not identical to Excel.

Frequently Asked Questions

Does Google Sheets COUNTIF ignore capitalization?
Yes. COUNTIF is not case-sensitive, so “tea,” “Tea,” and “TEA” satisfy the same text criterion. Use a case-sensitive method such as FIND inside SUMPRODUCT if capitalization must matter.

How do I count cells that contain a word anywhere?
Put an asterisk before and after the word in the criterion. With the cells in A2:A100 and the word “blue,” use =COUNTIF(A2:A100,"*blue*").

Can COUNTIF match only the start of a cell?
Yes. Put the search text first and an asterisk after it. For example, =COUNTIF(A2:A100,"Pro*") counts values beginning with “Pro.”

How do I search for an actual asterisk?
Use a tilde to escape it. With text in A2:A20, =COUNTIF(A2:A20,"*~**") counts cells containing a literal asterisk, with any text allowed before or after it.

Can COUNTIF search for text stored in another cell?
Yes. Join the cell reference to wildcard characters with ampersands. With the data in A2:A100 and the term in D2, =COUNTIF(A2:A100,"*"&D2&"*") searches for D2’s contents anywhere in each cell.

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.