To count cells that contain specific text in Excel, use the COUNTIF function with the text as the criteria, for example: =COUNTIF(A2:A20,"apple") counts every cell in A2:A20 that contains the exact word “apple”. For partial matches, wrap the text in asterisks: =COUNTIF(A2:A20,"*apple*") counts cells that contain “apple” anywhere in the text.
What COUNTIF Does and Its Syntax
COUNTIF is a built-in Excel function that counts the number of cells in a range that meet a single condition. Its syntax is:
=COUNTIF(range, criteria)
- range – the group of cells you want to evaluate (e.g., A2:A20).
- criteria – the condition that determines which cells to count. This can be a number, expression, cell reference, or text string.
The function works with numbers, dates, and text. When the criteria is text, you must enclose it in double quotes, as in "apple" or "*apple*". If you use a cell reference containing the text, you do not need quotes around the reference itself, but you still need quotes around any wildcards you add.
How Text Criteria Work: Exact vs. Partial Matches
By default, COUNTIF treats text criteria as an exact match unless you use wildcards. Here are the three wildcard characters you can use:
- * (asterisk) – matches any sequence of characters. For example,
"*apple*"matches “apple”, “pineapple”, “apple pie”, and “green apples”. - ? (question mark) – matches any single character. For example,
"sm?th"matches “smith” and “smyth” but not “smithy”. - ~ (tilde) – escapes a wildcard so you can match a literal asterisk or question mark. For example,
"~*"counts cells that contain an asterisk character.
If you want to count cells that start with “apple”, use "apple*". To count cells that end with “apple”, use "*apple". To count cells that contain “apple” anywhere, use "*apple*".
Note that COUNTIF is not case-sensitive. =COUNTIF(A2:A20,"APPLE") counts the same as "apple". If you need case-sensitive counting, you must use the SUMPRODUCT function with EXACT, like this: =SUMPRODUCT(--EXACT(A2:A20,"apple")).
How to Use a Cell Reference as the Criteria
Instead of typing text directly into the formula, you can reference a cell that contains the text. For example, if cell D1 contains “apple”, the formula =COUNTIF(A2:A20,D1) counts cells that exactly match the value in D1. To do a partial match with a cell reference, concatenate the wildcards using the ampersand (&):
=COUNTIF(A2:A20,"*"&D1&"*")
This counts cells that contain the text in D1 anywhere. This is useful when you want to change the search term without editing the formula each time.
Common Problems and Misconceptions
One common issue is that COUNTIF does not count cells that contain text but are formatted differently, or cells that have extra spaces. For example, if a cell contains ” apple” (with a leading space), "apple" will not match it. Use the TRIM function to clean data first, or use wildcards like "*apple*" which will still match because the asterisk accounts for the space.
Another misconception is that COUNTIF can count cells that contain multiple words in any order. It cannot. For instance, "*apple*" counts “apple pie” but not “pie with apple” because the asterisk only covers characters before and after the exact sequence “apple”. To count cells that contain both “apple” and “pie” in any order, you would need a more complex formula, such as =SUMPRODUCT(--(ISNUMBER(SEARCH("apple",A2:A20))*ISNUMBER(SEARCH("pie",A2:A20)))).
Also, COUNTIF only works with a single range and a single condition. For multiple conditions, use COUNTIFS.
Using COUNTIFS with Text Criteria
COUNTIFS allows you to apply multiple criteria across multiple ranges. For example, to count rows where column A contains “apple” and column B contains “red”, you would use:
=COUNTIFS(A2:A20,"*apple*",B2:B20,"red")
This counts only rows where both conditions are true. The same wildcard rules apply to each criteria. COUNTIFS is available in all modern versions of Excel.
Alternatives: SUMPRODUCT, FILTER, and Dynamic Arrays
If you need more flexibility than COUNTIF offers, consider these alternatives:
- SUMPRODUCT – can handle case-sensitive counts and multiple conditions without the limitations of COUNTIFS. For example,
=SUMPRODUCT(--(ISNUMBER(SEARCH("apple",A2:A20))))counts cells containing “apple” (case-insensitive). - FILTER – available in Excel 2021 and Microsoft 365, returns an array of matching values. You can wrap it in COUNTA to count them:
=COUNTA(FILTER(A2:A20,ISNUMBER(SEARCH("apple",A2:A20)))). - COUNTIF with wildcards – the simplest and fastest method for most text counting tasks.
For counting unique text values, use =SUMPRODUCT(--(COUNTIF(A2:A20,A2:A20)=1)) for exact matches, or the UNIQUE function in newer Excel versions.
Frequently Asked Questions
How do I count cells that contain specific text but not other text?
Use wildcards to exclude unwanted text. For example, to count cells that contain “apple” but not “pineapple”, use =COUNTIF(A2:A20,"apple*") which counts cells starting with “apple”. Alternatively, use =COUNTIF(A2:A20,"*apple*")-COUNTIF(A2:A20,"*pineapple*") to subtract the unwanted matches.
Can I count cells that are not equal to a specific text?
Yes, use the “not equal to” operator: =COUNTIF(A2:A20,"<>apple") counts all cells that are not exactly “apple”. Note that this counts blank cells as well because they are not equal to “apple”. To exclude blanks, use =COUNTIFS(A2:A20,"<>apple",A2:A20,"<>").
Why does COUNTIF return 0 when I know there are matches?
Check for leading/trailing spaces, non-printing characters, or the data being stored as a number instead of text. Use the TRIM and CLEAN functions to remove extra spaces and line breaks. Also ensure your criteria doesn’t have extra spaces inside the quotes.
Does COUNTIF work with numbers that are stored as text?
Yes, but you need to match the stored format. If numbers are stored as text, use the text criteria with quotes, like "123". If they are true numbers, use the number without quotes. To avoid confusion, use the VALUE function to convert text to numbers.
Can I use COUNTIF to count cells that contain any of several text values?
COUNTIF alone cannot handle multiple criteria in one range. Use multiple COUNTIF functions added together, like =COUNTIF(A2:A20,"apple")+COUNTIF(A2:A20,"orange"). For more complex logic, use SUMPRODUCT with OR conditions.
Is there a way to count partial matches that are case-sensitive?
Yes, use SUMPRODUCT with EXACT and wildcards is not possible because EXACT doesn’t support wildcards. Instead, use =SUMPRODUCT(--(ISNUMBER(FIND("apple",A2:A20)))) – FIND is case-sensitive, so it will only count cells with lowercase “apple”. For case-insensitive, use SEARCH instead.
Related
- Train-Test Split: Purpose, Methods, and Common Mistakes
- PostgreSQL: How to Execute a SQL String
- MS Access Split Form: Put the Datasheet on the Bottom
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Split Text in a Cell: Methods and FormulasYes. In Excel for Microsoft 365 on Windows, use Data > Text to Columns to split a cell’s…
- Excel COUNTIF Examples for Text, Dates, and NumbersExcel’s COUNTIF function counts cells in a range that match one condition. Its syntax is =COUNTIF(range,criteria); for example,…
- Excel Sort a List by Another List: Reliable MethodsTo sort Excel rows into the order of a second list, use SORTBY with XMATCH, or add a…
- Excel Formula for the First 3 Digits: Text or Numbers?To return the first three characters from a value in cell A2, use =LEFT(A2,3). The result is text,…
- Concatenate Names in Excel: Formulas and ExamplesTo concatenate names in Excel, join the cells with the ampersand operator and add a space between them:…
- Combine Cells in Excel That Have Words: Formulas and StepsTo combine words from two Excel cells, enter a formula such as =A2&" "&B2 in an empty cell;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.