Google Sheets COUNTIF Contains: Match Text Anywhere

To count cells in Google Sheets that contain a particular text anywhere, use =COUNTIF(A2:A100,"*apple*"). The asterisks are wildcards: they let any characters appear before or after “apple,” so the formula counts matches at the beginning, middle, or end of a cell.

What “contains” means in COUNTIF

COUNTIF checks a range against one condition and returns the number of cells that meet it. Its basic structure is =COUNTIF(range, criterion). For a contains search, the criterion is text wrapped in asterisks, such as "*apple*". The quotation marks identify the criterion as text, while the asterisks tell Sheets that other characters may occur around the search word.

For example, the criterion matches “apple,” “green apple,” “apple pie,” and “pineapple.” It does not require the searched text to be a separate word. This is useful for finding a fragment in product names, notes, email addresses, or other text, but it also means a search for “art” can match “party.” If whole-word matching matters, a basic COUNTIF wildcard criterion may be too broad.

COUNTIF counts matching cells, not every time the text appears. A cell containing “apple apple” contributes one to the result, just as a cell containing “apple” does. If you need to count occurrences within text rather than cells containing text, use a different approach.

How the wildcards work

Google Sheets supports two common wildcard characters in COUNTIF criteria. An asterisk (*) stands for any number of characters, including no characters. A question mark (?) stands for one character. The tilde (~) is used to treat a wildcard character as a literal character instead.

"*cat*" finds “cat” anywhere in a cell, including inside a longer word.
"cat*" finds text that begins with “cat.”
"*cat" finds text that ends with “cat.”
"c?t" can match “cat” or “cut,” because the question mark stands for one character.
"*~**" searches for a literal asterisk somewhere in the cell.

These wildcard rules apply to the criterion, not to the text stored in the range. For instance, putting * around a search term broadens the match; it does not modify the cell contents.

A worked example

Suppose A2:A7 contains “Green Apple,” “pineapple,” “Apple pie,” “Pear,” “green apple juice,” and one blank cell. With those values in that order, this formula counts four cells:

=COUNTIF(A2:A7,"*apple*")

The result is 4: “Green Apple,” “pineapple,” “Apple pie,” and “green apple juice” all contain the letters apple. “Pear” does not, and the blank cell does not contain the search text. Notice that capitalization does not change this result: COUNTIF is not case-sensitive for this kind of text comparison.

To narrow the search to cells that begin with the term, remove the first asterisk. With the same values in A2:A7, =COUNTIF(A2:A7,"apple*") returns 1 because only “Apple pie” begins with apple, ignoring case. The formula =COUNTIF(A2:A7,"*apple") returns 2: “Green Apple” and “pineapple” end with apple.

Use a cell as the search term

A search term does not have to be typed directly into the formula. If the range is A2:A100 and D2 contains the term to find, this formula adds wildcards around the value in D2:

=COUNTIF(A2:A100,"*"&D2&"*")

The ampersands join text together. If D2 contains apple, Sheets evaluates the criterion as *apple*. This setup is convenient when you want to change the search term without editing the formula. It also works well with a label such as “Search for” in C2 and an editable entry cell in D2.

There is one important detail: if D2 itself contains an asterisk or question mark, COUNTIF treats that character as a wildcard rather than as ordinary text. To search literally for characters that users may enter, escape them before adding the outer wildcards. With the data in A2:A100 and the literal search text in D2, use:

=COUNTIF(A2:A100,"*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),"?","~?")&"*")

The nested substitutions first escape a tilde, then an asterisk and a question mark. This order matters because the tilde is the escape character. For ordinary words without wildcard characters, the simpler formula is easier to read and is usually sufficient.

Case sensitivity and text matching

COUNTIF does not distinguish uppercase and lowercase letters in a text criterion. Therefore, *apple* can match “Apple,” “APPLE,” or “apple.” There is no case-sensitive switch you can add to COUNTIF. If capitalization must matter, use a case-sensitive function such as FIND in a formula that checks each cell.

With the search text in D2 and the cells to inspect in A2:A100, this formula counts cells where FIND locates the exact-case text:

=SUMPRODUCT(--ISNUMBER(FIND(D2,A2:A100)))

FIND returns a position when it finds the text and an error when it does not. ISNUMBER identifies successful results, and SUMPRODUCT adds the resulting true/false checks. For example, if D2 contains “Apple,” a cell with “Green Apple” matches, but a cell with “green apple” does not. This method is case-sensitive, unlike COUNTIF. It also searches for a literal text fragment rather than interpreting asterisks in D2 as COUNTIF wildcards.

For a case-insensitive alternative, SEARCH performs a similar text search without distinguishing case. However, COUNTIF is generally simpler when all you need is a count and wildcard matching is appropriate.

Count cells containing more than one term

Choose the formula based on whether a cell must contain one term or all of them. For a cell that must contain both “green” and “apple,” use COUNTIFS. With the text in A2:A100, this counts rows where both conditions apply to the same cell:

=COUNTIFS(A2:A100,"*green*",A2:A100,"*apple*")

For a cell that may contain either “apple” or “pear,” add two COUNTIF results. With names or descriptions in A2:A100, the formula is:

=COUNTIF(A2:A100,"*apple*")+COUNTIF(A2:A100,"*pear*")

This gives the number of apple matches plus the number of pear matches. A cell containing both terms is counted twice. That is correct if you want a total of term matches, but it is not a count of distinct cells that contain at least one of the terms. If your data can contain both, consider counting each row with a separate check or adjusting the method to fit whether you want matches per term or matching rows.

Common problems and useful checks

Missing a wildcard: =COUNTIF(A2:A100,"apple") looks for a criterion that matches the whole cell, rather than text with extra characters around it. Use "*apple*" for contains matching.
Unexpected extra matches: A search can match inside a longer word, as “art” does in “party.” Check whether substring matching is suitable for your data.
Searching for a literal wildcard: An unescaped * or ? has wildcard meaning. Use a tilde to escape it, or use the substitution formula when the search comes from an input cell.
Wrong range: Confirm that the first argument covers the cells you intend to count. A range such as A2:A100 excludes a header in A1.
Extra spaces or inconsistent data: Leading or trailing spaces can make exact matches surprising. For a contains search, a space within the search term is significant too. Check the actual cell contents if results seem off.
Blank search input: If D2 is empty in the cell-reference formula, the criterion becomes two wildcards. That can match many nonempty text cells, so consider leaving the result blank when no search term has been entered.

Formula separators can depend on a spreadsheet’s locale. The examples here use commas, as commonly shown in Google Sheets; if Sheets reports a formula parse error, check the locale under File > Settings and use the argument separator expected by that locale.

When COUNTIF is not the right tool

COUNTIF is a good choice when the question is “How many cells contain this text?” It returns one number and does not show the matching rows. If you want to review the matching records, use a filter or a function that returns rows rather than a count. A filter is often the simplest option for a one-time review; a formula-based result is more useful when the output needs to update automatically.

COUNTIF also cannot count the number of appearances of a word inside each cell. For instance, it counts a cell with “apple apple” once. If occurrences rather than matching cells are important, calculate occurrences separately, taking care with repeated phrases and overlapping matches. Likewise, if you need whole-word matching, account for word boundaries or separators in the data; wrapping a term in asterisks alone does not establish a word boundary.

Google Sheets also offers regular-expression functions for more specialized pattern matching, but those patterns require different syntax and are not COUNTIF wildcards. Use them only when the simple asterisk and question-mark rules cannot express the match you need. For a straightforward substring count, COUNTIF is typically easier to maintain and explain.

Frequently Asked Questions

Is COUNTIF contains case-sensitive in Google Sheets?
No. A COUNTIF text criterion such as "*apple*" matches uppercase and lowercase versions. Use a case-sensitive approach based on FIND if capitalization must match.

How do I count cells that contain a word anywhere?
Put an asterisk before and after the word in the criterion. For text in A2:A100, use =COUNTIF(A2:A100,"*word*"), replacing “word” with the text you want to find.

Can COUNTIF match part of a word?
Yes. The wildcard pattern searches for a character sequence anywhere in the cell, so it can match inside a larger word. For example, *apple* matches “pineapple.”

How can I use a search term from another cell?
Join the wildcard characters to the input cell. If the search term is in D2 and the data is in A2:A100, use =COUNTIF(A2:A100,"*"&D2&"*").

Does COUNTIF count repeated occurrences in one cell?
No. COUNTIF counts cells that meet the criterion. A cell with the search text several times still adds only one to the result.

Does the same wildcard pattern work in Excel?
Yes. The basic COUNTIF syntax and the asterisk, question-mark, and tilde wildcard conventions are also used in Excel. The article’s examples are written for Google Sheets in a web browser.

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.