Google Sheets CountIf Cell Contains Text: A Practical Guide
Learn how to count cells containing a substring in Google Sheets using COUNTIF, REGEXMATCH, and SUMPRODUCT. Includes practical examples, performance tips, edge cases, and reusable templates for 2026.
In Google Sheets, count cells that contain a specific substring using COUNTIF with wildcards. Example: =COUNTIF(A2:A100, "*sales*"). COUNTIF is case-insensitive and matches any part of the cell. For more complex patterns, REGEXMATCH with SUMPRODUCT can handle multiple terms or stricter casing.
Quick refresher: COUNTIF basics\n\nThe COUNTIF function counts cells within a range that meet a single condition. When your condition is text, you can use wildcards to capture partial matches. In many datasets, you want to know how many cells contain a particular substring rather than exactly matching the cell. This is common in keyword tracking, tag analysis, and simple data validation in Google Sheets. The classic contains-text pattern uses asterisks as wildcards to indicate "any characters before" and "any characters after." For example, to count cells that contain the word sales anywhere in the cell, you can use:\n\nexcel\n=COUNTIF(A2:A100, "*sales*")\n\n\nThis counts any cell in A2:A100 whose content includes sales, regardless of where it appears or what surrounds it. If you need to match multiple substrings, you can either sum multiple COUNTIF results or switch to more flexible patterns with REGEXMATCH and SUMPRODUCT. Additionally, remember that COUNTIF is not case-sensitive, so "Sales" and "sales" are treated the same.
Simple contains text example in a dataset\n\nImagine a sheet with product names in column A. You want to know how many entries mention the substring "pro". This is a textbook use of COUNTIF with a wildcard. The exact formula is:\n\nexcel\n=COUNTIF(A2:A500, "*pro*")\n\n\nInterpretation:\n- The asterisks allow any characters before or after the substring.\n- The formula returns the count of cells whose text contains the substring, regardless of position.\n- COUNTIF is case-insensitive, so "Pro" and "pro" are counted alike.\n\nIf you have a large data range, consider using a dynamic range (see the Performance section).
Counting with multiple substrings (OR logic)\n\nWhen you need to count cells containing either of two substrings, you can sum two COUNTIF results. This is simple, but be mindful of double-counting cells that contain both substrings. Example:\n\nexcel\n=SUM(COUNTIF(A2:A100, "*alpha*"), COUNTIF(A2:A100, "*beta*"))\n\n\nTo avoid double-counting, use a single REGEX-based formula that matches either term:\n\nexcel\n=SUMPRODUCT(--REGEXMATCH(A2:A100, "(?i)alpha|beta"))\n\n\nNotes:\n- The REGEX approach handles multiple substrings in one pass.\n- (?i) makes the match case-insensitive.\n- SUMPRODUCT returns a numeric count across the range.
Case sensitivity and advanced text matching\n\nCOUNTIF is generally case-insensitive in Google Sheets. If you need case-sensitive matching or more complex rules (whole words, boundaries), REGEXMATCH is your friend. A case-insensitive match for alpha or beta:\n\nexcel\n=SUMPRODUCT(--REGEXMATCH(A2:A100, "(?i)alpha|beta"))\n\n\nIf you want whole-word matching (e.g., match "text" but not "context"), use word boundaries:\nexcel\n=SUMPRODUCT(--REGEXMATCH(A2:A100, "(?i)\btext\b"))\n
Performance and dynamic ranges\n\nAs your dataset grows, fixed ranges become brittle. A dynamic range keeps counts accurate without manual edits. A common pattern is to count through all non-empty cells starting from A2:\n\nexcel\n=COUNTIF(INDIRECT("A2:A"&COUNTA(A:A)+1), "*text*")\n\n\nAlternative: use ARRAYFORMULA with TO_TEXT to normalize types when the range includes numbers:\nexcel\n=COUNTIF(ARRAYFORMULA(TO_TEXT(A2:A1000)), "*text*")\n\n\nTip: If you use Google Sheets with many rows, REGEXMATCH-based formulas can be faster for large datasets, especially when combined with SUMPRODUCT.
Practical variations and edge cases\n\nEdge cases matter. If your data includes numbers, you may need to convert to text first. Example using TO_TEXT for safety:\n\nexcel\n=COUNTIF(ARRAYFORMULA(TO_TEXT(A2:A1000)), "*text*")\n\n\nIf cells are blank, COUNTIF naturally excludes empty cells unless the criterion specifically matches an empty string. For counting cells containing multiple substrings with AND logic (both substrings present), use REGEXMATCH with a pattern like:\nexcel\n=SUMPRODUCT(--REGEXMATCH(A2:A1000, "(?i).*alpha.*beta.*|.*beta.*alpha.*"))\n\n
Reusable template and troubleshooting\n\nHere is a small Apps Script helper to count cells containing a substring without using formulas in sheets:\n\njavascript\nfunction countIfContains(substring, range) {\n var values = range.flat ? range.flat() : range;\n var needle = substring.toLowerCase();\n var count = 0;\n for (var i = 0; i < values.length; i++) {\n var v = values[i];\n if (typeof v === 'string' && v.toLowerCase().includes(needle)) {\n count++;\n }\n }\n return count;\n}\n\n\nUsage in Apps Script is optional for advanced users. For most day-to-day tasks, the COUNTIF/REGEXMATCH formulas in the sheet are faster and easier to audit. If you’re debugging, start with a simple string in a single cell and incrementally add ranges. Finally, verify results with a small test dataset to ensure expected behavior.
Example: end-to-end workflow in a blank sheet\n\n1) Prepare data in column A (A2:A20).\n2) Pick a target substring, e.g., "report".\n3) In B2 enter the simple contains text formula:\nexcel\n=COUNTIF(A2:A20, "*report*")\n\n4) If you need multiple terms, try the REGEX approach in C2:\nexcel\n=SUMPRODUCT(--REGEXMATCH(A2:A20, "(?i)report|summary"))\n\n5) Validate by adding sample rows that include the substrings and confirm the counts update automatically.\n
Steps
Estimated time: 15-25 minutes
- 1
Identify substring and target range
Scan your data to choose the substring you want to search for and the range to evaluate (e.g., A2:A100). This guides the COUNTIF usage and helps anticipate edge cases like mixed data types.
Tip: Start with a small sample range to verify the behavior before scaling. - 2
Apply a basic contains-text formula
Enter a simple COUNTIF with a wildcard to validate the contains-text pattern. This confirms the substring is detected anywhere in the cell.
Tip: Use a visible cell to compare the result with manual counts. - 3
Extend to multiple substrings if needed
If you need to count cells containing any of several substrings, decide between separate COUNTIF sums or a unified REGEXMATCH approach for efficiency.
Tip: For large datasets, REGEXMATCH with SUMPRODUCT is typically faster. - 4
Test with edge cases
Include blank cells, numbers, and mixed case to ensure formulas behave as expected. Verify case-insensitive vs case-sensitive needs.
Tip: Add a few test rows that contain exactly the target text, and others that miss it. - 5
Make it dynamic
Use a dynamic range or a named range so your counts grow with the dataset. This reduces maintenance over time.
Tip: Avoid hard-coded end row like A1000 unless you know data volume will not exceed it.
Prerequisites
Required
- Required
- Basic knowledge of Google Sheets formulas (SUM, IF, etc.)Required
- Open a Google Sheet where you want to apply COUNTIFRequired
Optional
- Optional: Apps Script access for advanced automationOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells or formulas | Ctrl+C |
| PastePaste values or formulas | Ctrl+V |
| FindSearch within the sheet | Ctrl+F |
| UndoRevert last action | Ctrl+Z |
| RedoReapply last undone action | Ctrl+Y |
FAQ
Does COUNTIF count numbers when using a text wildcard?
COUNTIF with a text wildcard searches text values. If a range contains numbers, they are treated as numbers and usually do not match a text pattern unless converted to text explicitly.
COUNTIF looks at text patterns; numbers typically don't match text wildcards unless you convert them to text.
How can I count cells containing multiple substrings at the same time?
Use a REGEX-based approach to match multiple substrings in one pass, for example: SUMPRODUCT(--REGEXMATCH(A2:A100, "(?i)alpha|beta"))
Use REGEXMATCH with SUMPRODUCT to count cells that contain any of the substrings in one formula.
Is there a way to count cells that contain an exact word, not part of a larger word?
Yes. Use REGEXMATCH with word boundaries, e.g., (?i)\btext\b, to match the whole word regardless of case.
Use word boundaries in REGEXMATCH to count exact words rather than substrings.
What is a quick pattern to count everything containing a specific substring across a column?
Use COUNTIF with a wildcard: =COUNTIF(A2:A, "*substring*"). This counts any cell containing the substring.
A simple wildcard COUNTIF counts any cell containing the substring.
How do I make the formula adapt as data grows?
Use dynamic ranges (A2:A) or INDIRECT with COUNTA to extend the endpoint automatically as new rows are added.
Make the range dynamic so the count updates when you add more data.
The Essentials
- Use substring with COUNTIF to detect contains-text.
- COUNTIF is generally case-insensitive in Sheets; REGEXMATCH enables exact rules.
- For multiple substrings, OR logic can be built with SUMPRODUCT + REGEXMATCH.
- Dynamic ranges improve long-term reliability of counts.