To count cells that contain a specific text string in Excel, use =COUNTIF(range,"*text*"). For example, =COUNTIF(A2:A10,"*apple*") counts every cell in A2:A10 that contains the word “apple” anywhere in it. This works because the asterisks (*) are wildcards that match any number of characters before and after the text.
How COUNTIF Handles Partial Matches
The COUNTIF function counts cells that meet a condition. When you use wildcards, it becomes a partial-match tool. The three wildcards in Excel are:
- * (asterisk) – matches any sequence of characters (including zero).
- ? (question mark) – matches any single character.
- ~ (tilde) – escapes the next character so you can search for a literal * or ?.
So =COUNTIF(A2:A10,"*cat*") counts cells like “cat”, “category”, “scatter”, and “catastrophe”. If you only want cells that start with “cat”, use "cat*"; if you want cells that end with “cat”, use "*cat". To count cells that contain a literal asterisk, use "~*".
Case Sensitivity and Exact Matches
COUNTIF is not case-sensitive by default. =COUNTIF(A2:A10,"apple") will count “Apple”, “APPLE”, and “apple” alike. If you need a case-sensitive count, you must use SUMPRODUCT with the EXACT function. For example, with text in A2:A10, this formula counts only cells that exactly match “apple” in case:
=SUMPRODUCT(--EXACT(A2:A10,"apple"))
This works because EXACT returns TRUE/FALSE for each cell, the double unary (–) converts TRUE/FALSE to 1/0, and SUMPRODUCT adds them up. For case-sensitive partial matches (e.g., contains “Apple” but not “apple”), you would need an array formula with ISNUMBER and SEARCH/FIND, but that is more advanced.
Counting Cells That Contain Any Text
A common question is how to count cells that are not empty (contain any text). The simple answer is =COUNTIF(A2:A10,"*") – this counts all cells with any text, but it also counts cells that contain a formula returning an empty string (“”). If you want to exclude formulas that return empty, use =SUMPRODUCT(--(LEN(A2:A10)>0)). Note that COUNTIF with “*” ignores numbers and dates, so if your range has numbers, they won’t be counted. To count all non-empty cells (including numbers), use =COUNTA(A2:A10).
Using COUNTIF with Cell References and Dynamic Text
Often you want to count cells that contain a value from another cell. You cannot put wildcards directly around a cell reference inside the quotes. Instead, you concatenate. Suppose B1 contains the text you want to search for. The formula is:
=COUNTIF(A2:A10,"*"&B1&"*")
This builds the pattern “*text*” from the cell value. If B1 contains “apple”, it becomes “*apple*”. Be careful: if B1 contains a wildcard character itself (like * or ?), it will be interpreted as a wildcard, not a literal. To treat the cell value as literal text, you would need to escape wildcards, which is complex; a simpler alternative is to use SUMPRODUCT with ISNUMBER and SEARCH.
Common Problems and Solutions
- Counting cells that contain multiple words: Use
=COUNTIFS(A2:A10,"*apple*",A2:A10,"*pie*")to count cells that contain both “apple” and “pie” anywhere. COUNTIFS supports multiple criteria on the same range. - Counting cells that contain one of several values: Use
=SUMPRODUCT(--((ISNUMBER(SEARCH("apple",A2:A10))+ISNUMBER(SEARCH("orange",A2:A10)))>0))– this counts cells that contain either “apple” or “orange”. - Counting cells that do NOT contain a text: Use
=COUNTIF(A2:A10,"*apple*")and subtract from total, or use=COUNTIF(A2:A10,"*apple*")with a not-equal approach:=COUNTIF(A2:A10,"<>*apple*")counts cells that are not exactly “apple” but that will still count cells containing apple. To count cells that do not contain apple, use=COUNTA(A2:A10)-COUNTIF(A2:A10,"*apple*"). - Counting cells with a specific number of characters: Use
=COUNTIF(A2:A10,"?????")to count cells with exactly 5 characters. The ? matches a single character.
Alternatives to COUNTIF for Contains
While COUNTIF is the simplest, other functions offer more flexibility. SUMPRODUCT with ISNUMBER and SEARCH allows case-insensitive partial matching and can handle arrays. For example, =SUMPRODUCT(--ISNUMBER(SEARCH("apple",A2:A10))) does the same as COUNTIF with wildcards but is more versatile for complex criteria. If you have Excel 365, you can use FILTER and COUNTA: =COUNTA(FILTER(A2:A10,ISNUMBER(SEARCH("apple",A2:A10)))). For case-sensitive partial matching, you would replace SEARCH with FIND. The choice depends on your needs: COUNTIF is fastest and easiest for single criteria; SUMPRODUCT is better for case-sensitivity or multiple conditions.
Frequently Asked Questions
Does COUNTIF count cells with numbers if I use “*”? No. The asterisk wildcard matches text only. To count cells that contain a number as part of a text string (like “order 123”), you can use =COUNTIF(A2:A10,"*123*") because the cell is text. But if the cell contains a pure number, COUNTIF with “*” will not count it. Use COUNTA for all non-empty cells.
How do I count cells that contain a specific text but not other text? Use COUNTIFS with two criteria. For example, to count cells that contain “apple” but not “pie”: =COUNTIFS(A2:A10,"*apple*",A2:A10,"<>*pie*"). This excludes cells that also contain “pie”.
Why does my COUNTIF return 0 when I know there is a match? Check for leading/trailing spaces in the data or the criteria. Wildcards match spaces as characters, so ” apple” will not match “*apple*”. Use TRIM to clean data, or use =COUNTIF(A2:A10,"*"&TRIM(B1)&"*").
Can I use COUNTIF with a named range? Yes, you can use a named range like =COUNTIF(MyRange,"*apple*"). The wildcards work the same way.
Does COUNTIF work in Google Sheets? Yes, COUNTIF and wildcards work identically in Google Sheets. The only difference is that Google Sheets uses the same syntax, so the formulas above work there too.
Is there a way to count cells that contain a text and are colored? COUNTIF cannot check cell color. You would need to use VBA or filter by color and use SUBTOTAL. For a formula-based approach, you would need to add a helper column with a formula that identifies the color, but that is not native.
Related
- JavaScript String split(): Syntax, Examples, and Tips
- Python r Strings: Raw String Meaning, Rules, and Examples
- Strings in R: Text, Character Vectors, and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Combine Unique Lists: 3 WaysTo combine two or more lists in Excel and keep only unique values, use the UNIQUE function with…
- Insert a Checkbox in Excel Using a Formula: Step-by-StepTo show a formula-driven checkbox in Excel, put a TRUE or FALSE value in A2 and, with that…
- COUNTIF Excel Range: What to Select and How It WorksIn Excel, the range in COUNTIF is the group of cells the function checks; the basic form is…
- Excel Insert Image from URL: Formula, Steps, and FixesTo insert an image from a URL in Microsoft Excel for Microsoft 365, put the direct image address…
- Find a Name in Excel: Search and Match MethodsTo find a name in an Excel sheet, press Ctrl+F on Windows, type the name, and press Enter…
- How to Compute Numbers in Excel with Formulas and FunctionsTo compute numbers in Excel, enter a formula in a cell beginning with an equals sign, then use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.