To count cells in Excel that contain a particular text anywhere in the cell, use COUNTIF with an asterisk on both sides of the text: =COUNTIF(A2:A20,"*apple*"). Replace the range and word with your own; the asterisks tell Excel to allow any characters before or after the text.
What COUNTIF Means by “Contains”
COUNTIF checks each cell in a range against one condition and returns how many cells meet it. Its basic form is =COUNTIF(range, criteria). For a contains search, the criterion uses the wildcard character *, which represents any sequence of characters, including no characters. A pattern such as *apple* therefore matches “apple” at the start, middle, or end of a cell.
For example, the pattern can match “red apple,” “apple juice,” and “pineapple.” It does not require the cell to consist of the search word alone. This is the key difference between a contains search and an exact match: the extra asterisks make other text around the target acceptable.
COUNTIF is useful for quick counts in lists, labels, product descriptions, notes, and other text-heavy ranges. It returns a number; it does not mark matching cells or return the matching text. If you need to inspect the individual matches, filtering or a separate formula may be more suitable.
Build a Contains Formula
Suppose descriptions are in A2:A8 and the search word is in E2. With those cells in place, use =COUNTIF(A2:A8,"*"&E2&"*"). The ampersands join the wildcard characters to the value in E2, so you can change the search word without editing the formula. If E2 contains “apple,” Excel evaluates the criterion as *apple*.
For a concrete example, imagine A2:A8 contains “Red apple,” “Apple juice,” “Green pear,” “Pineapple,” “apple,” a blank cell, and “Apple pie.” With “apple” in E2, the formula returns 5. It includes “Pineapple” because the letters apple occur inside that word, and COUNTIF does not distinguish uppercase from lowercase. It excludes the blank cell and “Green pear.”
For a one-off search, type the word directly into the criterion. With descriptions in A2:A8, =COUNTIF(A2:A8,"*apple*") returns 5 for the example list. A cell reference is usually easier to maintain, especially when you expect to search for several different words.
Contains Versus Exact and Starts-With Matches
The asterisks determine how much surrounding text Excel may accept. Remove both asterisks to require the whole cell to match the criterion. With the example list in A2:A8, =COUNTIF(A2:A8,"apple") returns 1: only the cell containing “apple” by itself qualifies. COUNTIF still treats “Apple” and “apple” as equivalent for this comparison.
Use just one asterisk to check one end of the cell. With descriptions in A2:A8, =COUNTIF(A2:A8,"apple*") counts cells that begin with “apple,” such as “Apple juice” and “Apple pie.” With the same range, =COUNTIF(A2:A8,"*apple") counts cells that end with “apple,” such as “Red apple.” These are useful distinctions when a list has a consistent naming convention.
When the search text is in E2, the starts-with version is =COUNTIF(A2:A8,E2&"*"), and the ends-with version is =COUNTIF(A2:A8,"*"&E2). In each case, the position of the wildcard determines whether extra characters are allowed before or after the search text.
Search for Text Entered in Another Cell
A reference-based formula is convenient for a small search box or a summary table. Put the desired phrase in E2 and the data in A2:A20. The formula =COUNTIF(A2:A20,"*"&E2&"*") counts cells containing the value in E2. When E2 changes, the count updates automatically, provided calculation is set to update normally.
This method works with phrases as well as single words. If E2 contains “New York,” the criterion searches for those characters together, including the space. It does not count a cell containing “New Jersey” just because it contains “New.” COUNTIF compares the complete text entered in E2 as part of the wildcard pattern.
One detail matters: asterisks and question marks inside E2 are not automatically treated as ordinary punctuation. Excel uses them as wildcards, even when they came from a cell reference. If the search value itself includes a literal wildcard character, escape it as described below or the result may be broader than intended.
COUNTIF Wildcards and Literal Characters
Excel’s principal COUNTIF wildcards are * and ?. An asterisk matches any number of characters; a question mark matches one character. The tilde ~ escapes a wildcard so Excel searches for that character literally. These rules apply to the criteria, whether you type the pattern directly or construct it with a cell reference.
*text*finds text anywhere in a cell.text*finds cells that start with text.*textfinds cells that end with text.~*represents a literal asterisk, and~?represents a literal question mark.~~represents a literal tilde.
For instance, if descriptions are in A2:A20 and you want cells containing an actual asterisk, use =COUNTIF(A2:A20,"*~**"). The first and last asterisks allow surrounding characters, while ~* means the middle asterisk must be present literally. This distinction is helpful when counting file patterns, coded labels, or text copied from systems that use these symbols.
Does COUNTIF Distinguish Uppercase and Lowercase?
No. COUNTIF is not case-sensitive, so a contains criterion for “apple” also matches “Apple” and “APPLE.” This is generally convenient for ordinary lists, where capitalization is inconsistent but the word is still the same. It also means that COUNTIF alone cannot count only the lowercase spelling.
For a case-sensitive contains count, use FIND inside SUMPRODUCT. With the example descriptions in A2:A8 and the search text in E2, =SUMPRODUCT(--ISNUMBER(FIND(E2,A2:A8))) returns 3 when E2 contains lowercase “apple.” FIND is case-sensitive, so it matches “Red apple,” “Pineapple,” and “apple,” but not “Apple juice” or “Apple pie.” SUMPRODUCT adds the TRUE matches after the double unary converts them to 1s.
Use this alternative only when letter case is meaningful in the data. It is more complex than COUNTIF, and a blank search cell is not a useful search criterion: check that the input cell contains the intended text before relying on the result.
Common Problems and How to Fix Them
- The result is zero: Check that the range includes the cells you expect and that the criterion is wrapped in quotation marks when typed directly. A contains criterion needs an asterisk at both ends, such as
"*word*". - The count is too high: Remember that a contains search also matches longer words. Searching for “art” can match “carton” or “artist.” Use an exact match if the whole cell must equal the word, or refine the search term if the data allows it.
- A word in the wrong column is being counted: Confirm that the first argument is the intended range. COUNTIF tests only that range; it does not search every column in the worksheet.
- The search value contains an asterisk or question mark: Excel interprets those as wildcards. Escape literal wildcard characters with a tilde, or adjust the criterion so it searches for the actual symbol.
- Some apparently empty cells affect results: A truly empty cell and a formula that displays an empty string are not always equivalent in criteria-based calculations. If your range contains formulas returning
"", check those rows separately when an exact count matters.
Also check for unexpected spaces. A cell containing “apple ” has a trailing space that is invisible in many views. A contains formula still finds “apple” within it, but an exact-match formula may not behave as expected. If exact matching is essential, inspect or clean the source values rather than assuming the displayed text tells the whole story.
When Another Excel Method Is Better
COUNTIF is a good choice when the goal is one count based on one condition. For a simple question such as “How many descriptions mention apple?”, its compact formula is usually easier to read and maintain than a more elaborate expression.
If the count must meet multiple conditions, COUNTIFS may be more appropriate, but it applies conditions to corresponding ranges; it is not simply a way to search several unrelated columns at once. If you need a list of matching records rather than a count, filtering the data or using a dynamic-array approach can be more useful. Microsoft 365 includes functions such as FILTER, but those return matching rows rather than the single total COUNTIF produces.
For a case-sensitive count, FIND with SUMPRODUCT is one alternative. For a visual review, use Excel’s worksheet search or filters to locate records and inspect them directly. Choose based on the output you need: a total, a filtered view, or a list of matching values.
Frequently Asked Questions
How do I count cells that contain a word anywhere in Excel?
Use COUNTIF with an asterisk before and after the word, for example =COUNTIF(A2:A20,"*apple*"). Replace A2:A20 and apple with your range and search term.
Can COUNTIF search for part of a word?
Yes. The wildcard pattern matches characters anywhere in the cell, so searching for “app” can match “apple” and “application.” If that creates unwanted matches, use a more specific search phrase or an exact-match criterion.
Is COUNTIF case-sensitive?
No. COUNTIF treats uppercase and lowercase letters as equivalent. For a case-sensitive contains count, use FIND with SUMPRODUCT.
How do I count cells that start with certain text?
Put the text first and an asterisk after it. For example, with data in A2:A20, =COUNTIF(A2:A20,"apple*") counts cells beginning with “apple,” regardless of capitalization.
Can COUNTIF count cells that contain a number?
COUNTIF can test for numeric criteria, but a contains search with wildcards is a text-pattern search. If you want to count cells containing a particular digit within longer numeric values, consider converting or storing the values as text and confirm that the intended matches are treated as text.
Practical Takeaway
For the common question “Does this cell contain this text?”, COUNTIF with *text* is the straightforward answer. Use a cell reference when the search term may change, remove one or both wildcards when you need a starts-with or exact match, and account for case sensitivity and wildcard characters when the data calls for more precise results.
Related
- Excel to PowerPoint Linked Cells: How to Link and Update
- Convert Excel to PowerPoint: Methods, Steps, and Tips
- Convert PowerPoint to Excel: Steps, Options, and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Password Protect a Sheet? What WorksNo Excel formula can password-protect a worksheet: formulas calculate values, but they cannot set sheet permissions or require…
- Replace Text in Excel: Find, Replace, and FormulasTo replace text in Excel for Microsoft 365 on Windows, press Ctrl+H, enter the text to find and…
- IFERROR Excel SUM: How to Ignore Errors in a TotalTo add a range while treating error cells as zero, use =SUM(IFERROR(A2:A10,0)), assuming the values and any errors…
- How to Find XLOOKUP in Excel and Use It in a FormulaTo find XLOOKUP in Excel for Microsoft 365 on Windows, open the Formulas tab and choose Lookup &…
- COUNTIF Calculation Examples: Text, Dates, and NumbersCOUNTIF counts cells in a range that meet one condition, such as a number, text value, date, or…
- Excel VLOOKUP Column Index Number ExplainedIn Excel VLOOKUP, the column index number tells Excel which column to return a value from, counting from…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.