Google Sheets COUNTIF: Count Cells That Contain Text

Yes. In Google Sheets, use COUNTIF with asterisks around the text to count cells that contain it anywhere: if the text is in A2:A20, enter =COUNTIF(A2:A20,"*apple*"). The asterisks act as wildcards, so this counts cells containing “apple” within longer text, not only cells whose entire contents are “apple.”

How COUNTIF interprets “contains”

COUNTIF checks each cell in a range against a criterion and returns the number of matches. For a contains search, the criterion needs a wildcard before and after the text. The first wildcard allows any characters to appear before the search text; the second allows any characters to appear after it. A cell such as “green apple pie” therefore matches a search for “apple.”

Without those wildcards, a text criterion normally looks for a cell whose value matches the specified text. For example, if product names are in A2:A20, this formula counts cells whose content is exactly “apple,” rather than every cell with that word inside a longer name:

With product names in A2:A20, use this formula to count exact matches for apple:

=COUNTIF(A2:A20,"apple")

COUNTIF text matching is not case-sensitive. A contains search for “apple” also matches “Apple” and “APPLE.” It counts matching cells, not the number of times the search text appears within each cell: one cell containing “apple apple” contributes one to the result.

Use wildcards for partial text

In Google Sheets criteria, an asterisk (*) represents any number of characters, including no characters, and a question mark (?) represents one character. A tilde (~) lets you search for a wildcard character literally rather than use it as a wildcard. These symbols make COUNTIF useful for partial names, codes, and phrases.

Text anywhere: put an asterisk on both sides, as in "*apple*".
Text at the beginning: put the asterisk only after the search text, as in "apple*". This matches “apple pie,” but not “green apple.”
Text at the end: put the asterisk only before it, as in "*apple". This matches “green apple,” but not “apple pie.”
One variable character: use a question mark, as in "A?C", to match three-character text with any one character between A and C.

If the cell must contain an actual asterisk, escape it with a tilde. With descriptions in A2:A20, this criterion looks for an asterisk anywhere in a cell:

=COUNTIF(A2:A20,"*~**")

Here the first and last asterisks mean “any characters,” while the middle ~* means a literal asterisk. This is useful for inventory notes or imported text that includes symbols.

Build the criterion from a cell

You do not have to type the search word into the formula. Referring to a separate cell lets you change the search without editing the formula. Suppose A2:A20 contains descriptions and B1 contains the word or phrase to find. Join the wildcards to B1 with the ampersand operator:

With descriptions in A2:A20 and the search phrase in B1, use:

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

If B1 contains apple, the result counts cells in A2:A20 that contain that text. The value in B1 can also be a phrase, such as “green apple.” If B1 is empty, the criterion becomes two wildcards; that can match text cells broadly, so avoid leaving the search cell blank when you want a meaningful filtered count. You can make the input expectation clear with a label such as “Search term” and a note asking users not to leave it empty.

A subtle point: wildcard characters typed into B1 are interpreted as wildcards too. If users may search for literal asterisks or question marks, they need to be escaped with a tilde in the criterion, or the formula needs to escape those characters before using the input. For ordinary words and phrases, the simpler formula above is usually the clearest choice.

Count text cells versus cells containing a word

“Count cells that contain text” can mean two different things: count all cells containing text of any kind, or count cells containing a particular word. The wildcard contains formula answers the second question. To count text entries regardless of their contents, use an asterisk as the entire criterion instead:

With mixed values in A2:A20, use this formula to count cells containing text:

=COUNTIF(A2:A20,"*")

This is different from counting every nonblank cell. A range can contain text, numbers, dates, or other values, and a text wildcard is intended to match text rather than numeric entries. If you want all populated cells, not just text entries, use a nonblank criterion:

With any kind of value in A2:A20, use this formula to count nonblank cells:

=COUNTIF(A2:A20,"<>")

Think about what the data represents before choosing. A cell displaying a number could be a numeric value or text that looks like a number; formatting can make the two appear alike. Also, a formula that displays an empty string is not always equivalent to a truly empty cell for every counting purpose. If your results seem off, inspect whether the cells contain formulas or imported values rather than relying only on how they look.

Count matches with more than one condition

COUNTIF applies one criterion. When the question involves multiple requirements—such as counting rows with “apple” in a description and “Open” in a status column—use COUNTIFS, which applies conditions to corresponding ranges. For example, if descriptions are in A2:A20 and statuses are in B2:B20, both ranges should cover the same rows.

With descriptions in A2:A20 and statuses in B2:B20, count rows containing apple whose status is Open:

=COUNTIFS(A2:A20,"*apple*",B2:B20,"Open")

This counts rows where both conditions are true for the same row. It is not the same as counting all apple descriptions and then adding all Open statuses; the criteria are combined as an AND condition. For an OR question, such as counting cells that contain either “apple” or “pear,” add the results of two COUNTIF formulas:

With descriptions in A2:A20, count cells containing apple or pear by adding the two counts:

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

This addition assumes a cell should be counted once for each matching term. If one cell contains both words, it contributes twice. If the goal is to count matching cells only once even when they contain both terms, the logic needs to avoid double-counting, for example by subtracting the number of cells matching both patterns.

Case-sensitive contains searches

COUNTIF ignores capitalization. That is normally helpful for names and descriptions, but it is not appropriate when uppercase and lowercase letters have different meanings in your data. Google Sheets’ FIND function is case-sensitive, and it returns a position when it finds the text or an error when it does not. ISNUMBER turns a successful position into TRUE, while SUMPRODUCT totals the matching TRUE values.

With text to check in A2:A20, count cells containing lowercase apple with a case-sensitive search:

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

The double unary operator (--) converts TRUE and FALSE results to 1 and 0 for summing. This formula counts “green apple” but does not count “Green Apple.” If you only need a standard contains search without regard to case, prefer COUNTIF: it is shorter and communicates the intent directly.

Common COUNTIF problems and fixes

When the result is zero or unexpectedly high, check the criterion and the underlying cell values before changing the formula. A few common issues account for many mismatches:

Missing wildcards: "apple" looks for an exact text match; "*apple*" searches within longer text.
Extra spaces: “apple” and “apple ” are different text strings in an exact match. Imported data may include leading, trailing, or repeated spaces. TRIM can help clean text, but it does not change the source cells unless you place cleaned results elsewhere.
Different spellings: COUNTIF does not treat “color” and “colour,” or singular and plural forms, as the same word. Choose a broader substring carefully or count multiple patterns.
Wildcard characters in the data: asterisks and question marks in the criterion have special meanings unless escaped. Use tilde when the symbol itself is what you want to find.
Wrong range: check that the formula covers all intended rows and does not include headers unless the header should be searched too.
Text that looks numeric: imported codes may be stored as numbers in some rows and text in others. Confirm the values and make the data type consistent if wildcard matching behaves unexpectedly.

Google Sheets uses a comma between function arguments in common English-language locales. Some spreadsheet locales use semicolons instead, so a formula copied from another locale may need its separators changed. If you see a formula parse error, check the separator and quotation marks first; use straight double quotes around text criteria.

Google Sheets and Excel differences

The core COUNTIF wildcard pattern is familiar in both Google Sheets and Microsoft Excel: an asterisk can stand for any number of characters, and the contains pattern places one before and after the search text. The examples here are for Google Sheets in a web browser. Excel also has COUNTIF and similar wildcard matching, but the menus, interface, and some formula behavior can differ between products and versions.

In Google Sheets, enter a formula directly into a cell or use the formula bar. The spreadsheet recalculates the result when the referenced data or search term changes. For text cleanup or visual identification rather than a numeric count, other tools may be more suitable: Find can locate text, while Format > Conditional formatting can highlight cells based on rules. Those features answer different questions; conditional formatting marks matching cells but does not itself provide the same count as COUNTIF.

Frequently Asked Questions

Does COUNTIF in Google Sheets ignore capitalization?
Yes. COUNTIF text criteria are case-insensitive, so a search for “apple” also matches “Apple.” Use a case-sensitive approach such as FIND combined with SUMPRODUCT when capitalization must matter.

How do I count cells that contain a phrase anywhere?
Put an asterisk on both sides of the phrase in the criterion. For example, with descriptions in A2:A20, =COUNTIF(A2:A20,"*green apple*") counts cells containing that phrase, even if other text appears before or after it.

Can COUNTIF count cells that contain either of two words?
Yes. Add separate COUNTIF results for the two contains patterns. Remember that a cell containing both words is counted in both results, so the sum can count that cell twice.

Why does COUNTIF return zero when I can see the word?
Check for a missing wildcard, unexpected spaces, a different spelling, or text stored in an unexpected form. Also confirm the range includes the cell and that quotation marks around the criterion are straight double quotes.

How can I count any text, not a specific word?
Use the criterion "*" to count text cells in the selected range. If you mean every nonblank cell, including numeric values, use a nonblank criterion instead, such as "<>".

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.