Count Cells Containing Specific Text in Excel and Google Sheets
Purpose
Counting how many cells in a range contain a specific word somewhere in the text — not an exact match,…
Counting how many cells in a range contain a specific word somewhere in the text — not an exact match, just the word anywhere inside the cell — is one of the highest-leverage counting patterns I know. How many support tickets mention the word Urgent? How many email addresses belong to the gmail.com domain? How many product descriptions contain the word Sale? The COUNTIF function handles all of these using wildcard characters in the criteria: an asterisk on each side of the search text says "anything before and anything after" so the word is counted wherever it appears in the cell. This is more specific than the broad count cells containing text formula — instead of "any text", you are looking for "this particular text" — and it is the pattern behind most text-based filtering and reporting I do. Once you know this formula and the count cells with partial text wildcard patterns, you can answer almost any substring-matching question with one line.
Syntax
✓ All versions
✓ Google Sheets
= Same syntax
=COUNTIF(range, "*text*")
Arguments
| Argument | Required | Description |
|---|---|---|
| range | Required | The cells to examine. Can be a specific range like A2:A100 or a column reference. Only text cells are evaluated by the wildcard match — numbers and blanks are skipped. |
| criteria | Required | The wildcard pattern as a quoted string, such as "*Error*" to match cells containing Error anywhere. For a dynamic search, concatenate using "*"&D1&"*" to pull the search term from cell D1. |
How it works
The asterisk in COUNTIF criteria is a wildcard that matches any sequence of characters, including an empty string. So "*Error*" matches any text that has zero or more characters, then the word Error, then zero or more characters — which is any cell that contains Error anywhere. Text matching is case-insensitive, so this pattern catches Error, error, ERROR, and mixed-case variants without you having to think about it. If the cell contains the search text more than once, it still only counts as one match — COUNTIF does not count occurrences within a cell. A subtle trap: because the wildcard matches zero or more characters, searching for "*cat*" will also match catalog, category, or scatter because those words contain the letters cat. If you need whole-word matching only, add spaces around the search text inside the wildcards, or use SUMPRODUCT with FIND for case-sensitive and more precise control. When you want the search term to come from another cell rather than being hard-coded, concatenate the wildcards with the cell reference using the & operator — the COUNTIF function accepts the built-up string transparently.
Examples
1
Count how many log entries contain the word Error anywhere in the message.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Log Message | Count with Error |
| 2 | 1 | System Error | =COUNTIF(B2:B11,"*Error*") |
| 3 | 2 | Warning | |
| 4 | 3 | Error 404 | 4 |
| 5 | 4 | Info | |
| 6 | 5 | Fatal Error | |
| 7 | 6 | Debug | |
| 8 | 7 | Connection Error | |
| 9 | 8 | OK | |
| 10 | 9 | Retry | |
| 11 | 10 | Success |
Row 2: 4
— Four log messages contain the word Error: System Error, Error 404, Fatal Error, and Connection Error. The other six messages do not contain the word Error anywhere.
The asterisk before Error matches any text that appears before the word (such as "System " or "Fatal ") and the asterisk after matches anything that follows (such as " 404"). The full text cell only needs to contain the word Error somewhere for it to count.
2
Count how many email addresses belong to the gmail.com domain by searching for @gmail.com anywhere in the address.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Count Gmail | |
| 2 | 1 | alice@gmail.com | =COUNTIF(B2:B11,"*@gmail.com*") |
| 3 | 2 | bob@yahoo.com | |
| 4 | 3 | carla@gmail.com | 5 |
| 5 | 4 | dan@hotmail.com | |
| 6 | 5 | ella@gmail.com | |
| 7 | 6 | finn@yahoo.com | |
| 8 | 7 | gia@outlook.com | |
| 9 | 8 | hugo@gmail.com | |
| 10 | 9 | iris@yahoo.com | |
| 11 | 10 | jo@gmail.com |
Row 2: 5
— Five email addresses contain @gmail.com: alice, carla, ella, hugo, and jo. The other five addresses use yahoo.com, hotmail.com, or outlook.com.
In this dataset the @gmail.com string only appears at the end, so the trailing wildcard does not add matches. You could also write "*@gmail.com" without the trailing asterisk and get the same result, which would be marginally more precise.
3
Use a cell reference for the search term. Cell A2 contains urgent, so count tickets containing that word anywhere, in any case.
| A | B | C | |
|---|---|---|---|
| 1 | Search | Ticket | Count |
| 2 | urgent | Server down - urgent | =COUNTIF(B2:B11,"*"&A2&"*") |
| 3 | Password reset | ||
| 4 | Email not sending - urgent | 5 | |
| 5 | Login issue | ||
| 6 | Urgent: data loss | ||
| 7 | Browser slow | ||
| 8 | URGENT escalation | ||
| 9 | File missing | ||
| 10 | urgent fix needed | ||
| 11 | Access request |
Row 2: 5
— Five tickets contain the word urgent in some form: "Server down - urgent", "Email not sending - urgent", "Urgent: data loss", "URGENT escalation", and "urgent fix needed". COUNTIF matches all casings.
Changing A2 to any other keyword — such as password or login — immediately updates the count. This pattern turns a static report into a keyword-search tool without writing any code.
Common use cases
1. Count support tickets that mention a specific issue keyword for volume tracking
=COUNTIF(A2:A500,"*refund*")
2. Count email addresses belonging to a specific domain from a contact list
=COUNTIF(B2:B1000,"*@gmail.com*")
3. Count product descriptions containing a promotional keyword like Sale or Offer
=COUNTIF(C2:C2000,"*Sale*")
4. Count tasks tagged with a specific label anywhere in the description
=COUNTIF(D2:D500,"*"&G1&"*")
5. Count feedback entries mentioning a specific feature name across reviews
=COUNTIF(E2:E3000,"*checkout*")
Common errors
Count is too high because short search text is matching inside other words
Wildcards match any characters, so "*cat*" also matches catalog, category, and vacation.
Fix: Add spaces inside the wildcards to match whole words only: "* cat *". For the first and last words in a cell, use separate COUNTIFs or switch to SUMPRODUCT with FIND for more control.
Count is zero when the text is clearly present
The search text is misspelled, or there is a hidden non-breaking space or special character in the cell that breaks the match.
Fix: Double-check the spelling in your criteria. Clean the source data with TRIM and CLEAN to remove invisible characters.
Case-sensitive match needed but COUNTIF is case-insensitive
COUNTIF always uses case-insensitive matching. If you need Error to not match error, COUNTIF cannot do it.
Fix: Use =SUMPRODUCT(--ISNUMBER(FIND("Error",A2:A100))) — FIND is case-sensitive so only exact-case matches are counted.
Tips and variations
Use a cell reference for the search text with concatenated wildcards
Put the search word in a cell and concatenate it between two asterisks using &. Change the cell and the count recalculates — this is the pattern behind every keyword-search dashboard I build with the COUNTIF function.
=COUNTIF(A2:A100,"*"&D1&"*")
Use starts-with or ends-with patterns to narrow the match
Drop one wildcard for position-specific matching. "PRD-*" matches cells that start with PRD-; "*@gmail.com" matches cells ending with @gmail.com. This reduces false positives when the search text could appear mid-string. Read more in the count cells with partial text wildcard patterns.
=COUNTIF(A2:A100,"PRD-*")
Sum multiple keyword searches with addition for OR logic
COUNTIF with wildcards handles one search term at a time. Add several COUNTIFs together to count cells containing any of several words. Be aware that a cell containing two of the words is counted twice in the sum.
=COUNTIF(A:A,"*Error*")+COUNTIF(A:A,"*Warning*")
Excel vs Google Sheets
Excel vs Google Sheets
COUNTIF with wildcard substring matching works identically in Excel 2003 and newer, Excel 365, and every version of Google Sheets. The asterisk wildcard behaviour and case-insensitive matching are the same in both applications. Formulas copy between the two without modification.
Frequently asked questions
Use =COUNTIF(range, "*text*") where the asterisks are wildcards that match any characters before and after the search text. For example, =COUNTIF(A2:A100,"*Error*") counts every cell that contains the word Error anywhere in the text. The same formula works in Google Sheets.
No. COUNTIF is case-insensitive, so "*Error*" matches Error, error, ERROR, and every mixed-case variation. If you need a case-sensitive count, use SUMPRODUCT with FIND: =SUMPRODUCT(--ISNUMBER(FIND("Error",A2:A100))). FIND is case-sensitive while SEARCH is not.
Concatenate the wildcards with the cell reference using &: =COUNTIF(A2:A100,"*"&D1&"*"). Put the search word in D1, and the formula counts cells containing whatever text D1 holds. Change D1 and the count updates automatically.
Not directly with a single COUNTIF. You can add multiple COUNTIFs together: =COUNTIF(A:A,"*Error*")+COUNTIF(A:A,"*Warning*"). Be aware that a cell containing both words would be counted twice in this sum. For cleaner OR logic, SUMPRODUCT with ISNUMBER and SEARCH handles it without double-counting.
The asterisks on either side of the search text match anything, so a search for "*cat*" will also match words like catalog, category, and indicate (because it contains the letters c-a-t). To match whole words only, add spaces inside the wildcards — "* cat *" — and remember to handle words at the start or end of the cell separately.