COUNTIF Text in Google Sheets: Examples and Tips

To count text in Google Sheets, use COUNTIF with the cells to check and the text criterion, such as =COUNTIF(A2:A100,"Apple"). It counts cells that match the criterion without regard to capitalization; use wildcard characters such as * to count text that contains a word rather than matching the whole cell.

What COUNTIF Does in Google Sheets

COUNTIF counts how many cells in one range meet one condition. It is useful for questions such as how many orders have the status “Shipped,” how often a product appears in a list, or how many comments contain a particular word. The function returns a number, not a filtered list of the matching entries.

The basic structure is COUNTIF(range, criterion). The range is the group of cells to inspect; the criterion tells Sheets what qualifies. The range can be a column segment such as A2:A100, and the criterion can be a text value, a wildcard pattern, a comparison, or a reference to another cell. Keep the range to the data you intend to count when practical, so the formula is easier to understand and the results are less likely to include unrelated entries.

COUNTIF applies one condition to one range. It is not a special text-only function: the same function can count numbers and other values too. For text questions, the key is choosing whether you mean an exact cell match, a partial match, or a condition based on text stored elsewhere.

Count an Exact Text Match

Use an exact text criterion when the entire cell should equal a particular word or phrase. With product names in A2:A100, this formula counts cells whose value is Apple:

=COUNTIF(A2:A100,"Apple")

For example, if Apple appears in seven cells in that range, the result is 7. A cell containing “Green Apple” does not qualify, because the full cell is not an exact match. COUNTIF is case-insensitive for text, so “apple,” “Apple,” and “APPLE” are treated as the same criterion for this purpose.

Text criteria need quotation marks when written directly in a formula. The quotation marks mark the text value; they are not part of the text being searched for. If a word contains an apostrophe, such as “Children’s,” the apostrophe can remain inside the double quotation marks. For a criterion containing a double quotation mark, the formula needs additional quote handling, so referencing a cell containing the text is often clearer.

Count Text That Contains a Word

To find a word anywhere inside a cell, put an asterisk before and after the word. The asterisk is a wildcard representing any sequence of characters, including no characters. With descriptions in A2:A100, this counts cells containing the word apple as part of their text:

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

This can count entries such as “apple juice,” “green apple,” and “Pineapple,” since the letters apple occur within each cell. It is still a text pattern match, not a linguistic search: the formula does not distinguish the word “apple” from those letters inside a longer word.

Other useful wildcard patterns include:

"apple*" matches text that begins with apple, such as “apple juice.”
"*apple" matches text that ends with apple, such as “green apple.”
"A?C" matches a three-character pattern that starts with A and ends with C, with any one character in the middle.

The question mark (?) stands for one character, while the asterisk stands for any number of characters. Both are wildcard operators in COUNTIF criteria. If the character you want to search for is itself an asterisk or question mark, precede it with a tilde (~) in the criterion to treat it as a literal character. Wildcards are handy for flexible matching, but they can also make a criterion broader than intended, so inspect a few of the matching source cells when accuracy matters.

Use a Cell as the Text Criterion

A criterion does not have to be typed into the formula. If the word to count is in another cell, refer to that cell. With names in A2:A100 and the requested name in D2, use:

=COUNTIF(A2:A100,D2)

Now changing D2 updates the count without editing the formula. This setup is useful for a small summary table: put different categories in D2, D3, and D4, then copy the formula down beside them. When copying, Sheets adjusts a relative reference such as D2 to D3. If the range should stay fixed while you copy, use absolute references; with the data in A2:A100 and criteria in D2:D10, the formula for the first summary row is =COUNTIF($A$2:$A$100,D2). The dollar signs keep the source range fixed while D2 changes as the formula is filled down.

You can add wildcards around a cell reference with the ampersand (&), which joins text pieces together. With descriptions in A2:A100 and the search word in D2, this counts cells containing the current D2 value:

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

If D2 contains “apple,” the criterion becomes *apple*. If D2 is blank, the resulting pattern is effectively **, which may match a much broader set of text than intended. Keep the search cell nonblank or add a separate check if an empty search should return zero.

Count Text Values and Avoid Confusing Blanks

Sometimes the goal is not to count one particular word but to count cells containing text. COUNTIF can use a wildcard criterion for this. With entries in A2:A100, the formula =COUNTIF(A2:A100,"*") counts text entries that match the wildcard pattern. Check the result against your sheet if the range includes formulas, blanks, or mixed types, because “has text” may not mean the same thing as “is not empty” in every dataset.

Blank cells, empty strings, and cells that look blank can lead to confusing totals. A cell may appear empty because it contains spaces, or because a formula returns an empty string. Those cases are not necessarily equivalent to a truly empty cell. If a count seems off, click a few cells that appear blank and inspect the formula bar or formula itself. For messy imported data, cleaning the source or creating a helper column with normalized values can be more reliable than trying to build every exception into one COUNTIF condition.

Likewise, a cell with leading or trailing spaces may not match the intended word as expected. “Apple” and “Apple ” look nearly identical on screen but are different text values. To prevent this problem, clean the original data where possible; TRIM can remove ordinary extra spaces in a helper column. Be aware that cleaning a dataset changes or supplements its values, so preserve the original column if those spaces carry meaning.

Case Sensitivity and Multiple Conditions

COUNTIF does not distinguish uppercase from lowercase. That behavior is convenient for ordinary category counts, but not when capital letters define separate codes. With values in A2:A100, this formula counts only case-sensitive exact matches to Apple:

=SUMPRODUCT(--EXACT(A2:A100,"Apple"))

EXACT compares text with case sensitivity, and SUMPRODUCT adds the resulting match indicators. For instance, a cell containing “apple” will not qualify when the criterion is “Apple.” This is a more specialized formula, so use COUNTIF when capitalization should not matter.

COUNTIF handles one condition. If a record must meet two or more conditions, use COUNTIFS, which applies multiple criteria to corresponding ranges. With statuses in A2:A100 and regions in B2:B100, this counts rows that have the status Shipped and the region West:

=COUNTIFS(A2:A100,"Shipped",B2:B100,"West")

Each criteria range should cover the same set of rows. If the goal is to count either of two separate text values in one column, add two COUNTIF results. With statuses in A2:A100, this counts cells equal to Shipped or Pending:

=COUNTIF(A2:A100,"Shipped")+COUNTIF(A2:A100,"Pending")

That addition is appropriate for distinct exact values. For more complicated combinations of “either this or that” and other conditions, write out the logic carefully and verify it with a small hand-checked sample.

Common COUNTIF Text Problems

If the result is zero when matching entries appear to be present, first check the matching rule rather than assuming the function is broken. An exact criterion will not count text that merely contains the word. Conversely, a wildcard criterion can count a longer word containing the same letters. Also check for extra spaces, spelling differences, punctuation, and whether the formula points to the intended range.

Wrong range: Confirm that the range includes the data rows and excludes headers or unrelated columns.
Criterion typed as text: Direct text criteria need quotation marks, for example "Shipped".
Wildcard used unintentionally: A * or ? in the criterion changes the match from literal text to a pattern.
Spaces or imported characters: Visually similar entries may contain extra spaces or different punctuation.
Copied formula shifted: Use dollar signs around a range that should not move when filling formulas down.

Google Sheets formulas commonly use commas between arguments, as shown here. Depending on a spreadsheet’s locale, Sheets may display or expect semicolons instead. If a formula copied from a comma-based example produces a parse error, check File > Settings for the spreadsheet’s locale and use the separator appropriate to that setting. Excel also has COUNTIF and COUNTIFS, but menu locations and locale-specific separators can differ; the examples here are for Google Sheets in a browser.

When to Use Another Function

COUNTIF is the simplest choice when one range and one condition are enough. Use COUNTIFS when every counted row must satisfy multiple conditions. Use a case-sensitive approach such as EXACT with SUMPRODUCT only when letter case is genuinely meaningful. If the task is to display the matching records rather than return a count, use a filtering method instead of COUNTIF; a count cannot show which cells qualified.

For a recurring report, consider placing criteria in cells and using fixed source ranges. This makes the formula easier for another person to audit and lets the report update as the criteria change. For a one-off check, a direct formula with a quoted word may be quicker. In either case, test the formula on a range small enough to inspect manually, especially when wildcards or data imported from another system are involved.

Frequently Asked Questions

Is COUNTIF case-sensitive in Google Sheets?
No. COUNTIF text criteria are case-insensitive, so Apple and apple count as the same text for a standard COUNTIF match. Use a case-sensitive alternative involving EXACT if capitalization must distinguish values.

How do I count cells containing a word, not just equal to it?
Put an asterisk before and after the word in the criterion, such as "*apple*". The wildcard allows other characters before or after the word in the same cell.

Can COUNTIF use a word stored in another cell?
Yes. Put the search word in a cell and use that cell as the criterion, for example =COUNTIF(A2:A100,D2) when the list is in A2:A100 and the criterion is in D2.

How can I count two different text values?
Add two COUNTIF formulas when either exact value should be counted. Use COUNTIFS instead when each row must meet multiple conditions at the same time.

Why does COUNTIF return zero when the text looks identical?
Check whether the formula expects an exact match while the cell contains extra words, spaces, punctuation, or a different spelling. Confirm the selected range and inspect the cell contents rather than relying only on how they look.

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.