Google Sheets COUNTIF: Count Cells Containing Text

With the text to check in A2:A100, count cells containing a word or phrase anywhere in the cell with =COUNTIF(A2:A100,"*apple*"). The asterisks are wildcards: they allow any characters before and after “apple.” Google Sheets COUNTIF is not case-sensitive, so this also counts cells containing “Apple” or “APPLE.”

How the contains criteria works

COUNTIF takes a range and a condition, then counts cells in that range that meet the condition. To look for a substring rather than an exact whole-cell value, put an asterisk on each side of the search text. The resulting criterion means “any characters, then this text, then any characters.”

The wildcard characters have different jobs:

* matches any sequence of characters, including no characters.
? matches exactly one character.
~ can be used to treat a following asterisk or question mark as a literal character rather than a wildcard.

For example, *cat* can match “cat,” “copycat,” or “cat food.” It searches anywhere in each cell, not just at the beginning. COUNTIF counts a matching cell once, even if the search word appears several times in that cell. It does not count each occurrence of the word separately.

A simple example with a range

Suppose A2:A8 contains “Red apple,” “Green apple,” “Pineapple,” “Apple juice,” “red pepper,” “Gala apple,” and a blank cell. With that exact layout, use =COUNTIF(A2:A8,"*apple*"). The result is 5: the first, second, third, fourth, and sixth entries contain the letters apple. The blank cell and “red pepper” do not qualify.

Notice that “Pineapple” counts even though “apple” is part of a longer word. The formula is looking for a sequence of characters, not a separate word. If you only want a standalone word, COUNTIF with asterisks does not distinguish word boundaries. A regular expression is a better fit when you need to define boundaries or more complicated text patterns.

The range should cover the cells you want counted, and it is usually best to avoid including a header row. A bounded range such as A2:A100 is clear and may be easier to manage than checking an entire column. If you do use a whole-column reference, make sure it is intentional and that the column contains the kind of data you mean to count.

Use a cell as the search term

Typing a different word into the formula each time is inconvenient when the search changes. Put the term in a separate cell and join it to the wildcard characters with the ampersand operator. With searchable text in A2:A100 and the term in D2, use =COUNTIF(A2:A100,"*"&D2&"*"). If D2 contains “apple,” the formula behaves like the fixed-phrase example and returns 5 for the sample list.

This approach is useful for a small search box on a report: change D2 and the count updates without editing the formula. If D2 is empty, the criterion becomes two asterisks; that is not a useful “no search” state and can produce a broad count of text entries. To prevent a misleading result, keep the input populated or wrap the count in an IF check. With the search term in D2 and the data in A2:A100, this version displays zero when D2 is blank: =IF(D2="",0,COUNTIF(A2:A100,"*"&D2&"*")).

COUNTIF interprets wildcard characters in the search term as pattern instructions. Thus, if D2 contains an asterisk, it will not necessarily search for a literal asterisk. That distinction matters when the source data includes codes, filenames, or other text that uses characters such as * or ?.

Make matching case-sensitive

COUNTIF does not distinguish uppercase from lowercase. That is normally convenient for names, labels, and everyday descriptions, but it is not appropriate when capitalisation changes the meaning of a code. For example, you may need to count “ab” but not “AB.”

For a case-sensitive substring count, Google Sheets can use REGEXMATCH inside SUMPRODUCT. With text values in A2:A100, use =SUMPRODUCT(--REGEXMATCH(A2:A100,"ab")). In a range containing “ab-12,” “AB-12,” and “xxabxx,” the result is 2: the first and third cells match the lowercase sequence. REGEXMATCH looks for the pattern anywhere in the text unless the pattern is anchored, and its regular-expression matching is case-sensitive by default.

Regular expressions have their own special characters, so this alternative is not a drop-in replacement for every literal search. A period, for instance, has a special meaning in a regular expression. If the text to search is user-entered and may contain regex symbols, account for those symbols or use a simpler method where possible. Also check the range for errors, because an error in an input cell can affect a formula that evaluates the range.

Search for literal asterisks and question marks

When a search term contains * or ?, COUNTIF may read those characters as wildcards. Prefix a wildcard with a tilde to make it literal: ~* searches for an asterisk, while ~? searches for a question mark. A literal tilde itself is represented with ~~.

For a dynamic search that treats the contents of D2 literally, escape the tilde first, then the asterisk and question mark. With the text to inspect in A2:A100 and the literal search term in D2, use =COUNTIF(A2:A100,"*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"~","~~"),"*","~*"),"?","~?")&"*"). The order matters: escaping existing tildes first prevents the tildes added to escape wildcard characters from being escaped a second time.

This longer version is only needed when the search input may include wildcard symbols and you want those symbols matched as ordinary characters. For a controlled search term such as “apple,” the shorter cell-reference formula is easier to read and maintain.

Common reasons the count is unexpected

If the result is too high or too low, check the criterion and the data before changing functions. These are frequent sources of confusion:

It finds part of a word. “Pineapple” matches “apple” because the formula searches for a substring. Use a boundary-aware regular expression if you need a whole word.
Capitalisation seems ignored. That is expected with COUNTIF. Use a case-sensitive method such as REGEXMATCH when upper- and lowercase must differ.
The search term contains * or ?. Those characters act as wildcards unless escaped with a tilde.
The formula returns zero. Confirm that the selected range contains the expected text and that the search term has no extra spaces or spelling differences. A leading or trailing space in the searched text can prevent a match.
The count includes unexpected cells. Check whether the range includes a heading, notes, or other data. Remember that a partial match is enough.
The cell looks like it contains the term, but does not match. The visible display can differ from the underlying value. Check for nonprinting characters, line breaks, or a different Unicode character that looks similar.

For a quick data check, compare a suspicious value in the formula bar and consider using TRIM to remove ordinary leading or trailing spaces in a cleaned-up helper column. TRIM is not a universal cleanup tool for every invisible character, so it may not resolve line breaks or unusual spacing. It is safer to inspect representative cells than to assume the displayed text tells the whole story.

COUNTIF compared with other ways to count

COUNTIF is usually the simplest choice when the requirement is “count cells containing this text, without caring about case.” It is concise, updates automatically when the range changes, and avoids a separate helper column. It is also more readable to a spreadsheet user who only needs a basic wildcard condition.

Use REGEXMATCH when the matching rule has structure that wildcards cannot express, such as a word boundary, alternatives, or a particular pattern of characters. For example, regular expressions can distinguish a word from the same letters embedded in a longer word. The trade-off is that regex syntax is more complex and its special characters need to be handled carefully.

Use a filter rather than a count when the goal is to see or work with the matching rows. In Google Sheets, select the data and choose Data > Create a filter, then use the column’s filter controls to narrow the displayed records. A count tells you how many cells match; a filter helps you inspect the actual records. If the question is how many times a substring appears in total, rather than how many cells contain it, COUNTIF is not the right measure because repeated occurrences within one cell still contribute only one to the count.

Excel also supports COUNTIF with wildcard criteria, so the basic formula is familiar across both spreadsheet programs. However, this article uses Google Sheets in a web browser, and Google Sheets-specific functions such as REGEXMATCH may not have a direct equivalent with identical behavior in every Excel version. If you share a workbook between programs, verify formulas and regex behavior in the destination application.

Frequently Asked Questions

Does Google Sheets COUNTIF distinguish uppercase and lowercase?
No. COUNTIF matching is case-insensitive, so “apple” can match “Apple.” For a case-sensitive substring count, use a method based on REGEXMATCH and confirm that the pattern is suitable for regular-expression matching.

How do I count cells containing a phrase, not just one word?
Put the phrase between asterisks in the criterion. With text in A2:A100, a fixed phrase can be counted with =COUNTIF(A2:A100,"*red apple*"). The phrase must appear in that order, but other characters may come before or after it.

Can COUNTIF count a word only when it is a whole word?
Not reliably with the simple asterisk pattern. The pattern matches substrings, so “cat” also matches “copycat.” Use a regular expression with appropriate word boundaries when whole-word matching is required, and test it against examples from your data.

How do I count cells containing text from another cell?
With the search term in D2 and the text range in A2:A100, use =COUNTIF(A2:A100,"*"&D2&"*"). The count updates when D2 changes. If D2 may contain * or ?, escape those characters first so they are treated literally.

Does COUNTIF count multiple occurrences in one cell?
No. COUNTIF counts qualifying cells, not appearances. A cell containing “apple apple” adds one to the result. Counting every occurrence requires a different calculation that measures how many times the substring occurs within each cell.

Why does my formula count text inside a longer word?
Because the asterisks allow any characters around the search text, and COUNTIF does not enforce word boundaries. A search for “art” can match “artist.” Use a boundary-aware regular expression if that is not the intended match.

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.