To count cells that contain any text from a list in Excel for Microsoft 365, use =SUM(COUNTIF(A2:A10,"*"&C2:C4&"*")), where A2:A10 contains the text to search and C2:C4 contains the terms. This adds one match for each list term found; if one cell contains two terms, that cell contributes two to the total.
What the formula counts
COUNTIF normally checks a range against one criterion. The asterisks on either side of each list entry are wildcard characters: they allow any characters before and after the term. For example, the criterion "*"&C2&"*" matches a cell that contains the text in C2 anywhere within its contents. SUM adds the results for all the terms in the list.
Suppose A2:A7 contains Red apple, Green pear, Apple juice, Pear and apple, Banana, and Pineapple, while C2:C3 contains apple and pear. With A2:A7 as the search range and C2:C3 as the keyword list, the formula returns 6: apple matches four cells and pear matches two. The combined row Pear and apple contributes twice. Pineapple also matches apple, because the formula looks for a substring, not a whole word.
That last detail is important: “contains” means that the term can appear as part of a longer string. If a list contains cat, for instance, the wildcard criterion can match cats or category as well as a cell containing just cat. Choose a different method if you need whole-word or exact-cell matching.
Set up the ranges and enter the formula
Keep the data and search terms in separate ranges, with one item per cell. For example, put product descriptions or names in A2:A10 and the words to look for in C2:C4. In an empty cell, enter the formula below. It is suitable for Microsoft 365, where COUNTIF can evaluate the list of criteria as an array.
With search text in A2:A10 and terms in C2:C4, enter:
=SUM(COUNTIF(A2:A10,"*"&C2:C4&"*"))
Press Enter. The cell displays the total number of term-to-cell matches. If the data range or list grows, update the references or use larger ranges that do not include unrelated text. Avoid including a heading in either range unless you want Excel to treat it as data.
To make the formula easier to copy between cells, use absolute references for the list. With the same layout, this version keeps C2:C4 fixed when copied:
=SUM(COUNTIF(A2:A10,"*"&$C$2:$C$4&"*"))
The dollar signs lock the list range; they do not change the matching rule. On Windows, F4 cycles through reference-locking options while editing a reference in a formula. On Mac, the shortcut can depend on the keyboard and system settings; inserting the dollar signs directly is an alternative.
Count matching cells only once
The basic SUM formula counts matches by keyword, not distinct rows. That is useful when you want to know how many keyword hits occurred, but it may overstate the number of records that contain something from the list. To count each matching cell once, test each cell against all the terms and add one if any term is found.
With data in A2:A7 and the list in C2:C3, use this Microsoft 365 formula:
=SUM(--BYROW(A2:A7,LAMBDA(cell,SUM(--ISNUMBER(SEARCH($C$2:$C$3,cell)))>0)))
For the example data above, the result is 5: five cells contain apple or pear, even though the basic formula returns 6 term-to-cell matches. BYROW evaluates one data cell at a time. SEARCH checks that cell against the list, ISNUMBER identifies successful searches, and the inner SUM determines whether at least one term matched. The outer SUM counts the rows that passed.
BYROW and LAMBDA are available in Microsoft 365 and supported newer Excel releases; if Excel reports an unrecognized function, use the helper-column approach below. This distinct-row formula also assumes the list has no blank entries, because searching for an empty string can behave like a match. Remove blank cells from the keyword range.
Use a helper column for a transparent check
A helper column makes the result easier to inspect row by row and can be useful if you do not want a single array formula. With data in A2:A10 and terms in C2:C4, enter the following in D2, then fill it down through D10:
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$4,A2)))>0
For each row, the formula returns TRUE if at least one listed term appears in the corresponding A cell, and FALSE otherwise. With those results in D2:D10, count the TRUE values using:
=COUNTIF(D2:D10,TRUE)
The result is the number of matching data rows, with each row counted once. The helper column also helps locate unexpected results: filter column D for TRUE, then review the matching text. As with the other examples, remove blank list cells so an empty term cannot make a row appear to match.
Choose between contains, exact match, and case-sensitive search
Use the wildcard COUNTIF formula when a term can occur anywhere within a longer cell value and you want case-insensitive matching. COUNTIF does not distinguish uppercase and lowercase, so apple matches Apple. SEARCH, used in the distinct-row examples, is also case-insensitive.
If the entire cell must equal one of the list entries, omit the asterisks. With values in A2:A10 and exact-match criteria in C2:C4, use:
=SUM(COUNTIF(A2:A10,C2:C4))
This formula counts exact cell values against each list item. It does not count a cell containing “Red apple” as equal to the list item “apple.” As with the contains formula, a matching cell can be counted more than once if the criteria list contains repeated entries.
For a case-sensitive contains check while counting distinct cells, use FIND instead of SEARCH in the BYROW formula. With data in A2:A7 and terms in C2:C3, enter:
=SUM(--BYROW(A2:A7,LAMBDA(cell,SUM(--ISNUMBER(FIND($C$2:$C$3,cell)))>0)))
FIND distinguishes uppercase and lowercase, so a lowercase term will not match the same letters written only in uppercase. It still searches for a substring, not a whole word. If you need case-sensitive counts per keyword rather than distinct matching rows, the approach needs to evaluate each term separately.
Wildcards, punctuation, and blank list cells
In COUNTIF criteria, an asterisk means “any number of characters,” and a question mark means “any single character.” A tilde escapes a wildcard when it is meant literally. Therefore, a list term such as C++ usually behaves as ordinary text, but a term containing * or ? can be interpreted as a pattern rather than as literal punctuation.
To treat asterisks, question marks, and tildes in the list as literal characters, escape them before adding the surrounding wildcards. With search text in A2:A10 and terms in C2:C4, use:
=SUM(COUNTIF(A2:A10,"*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2:C4,"~","~~"),"*","~*"),"?","~?")&"*"))
The nested SUBSTITUTE functions escape a tilde first, then an asterisk and question mark. This matters when the list itself contains wildcard characters that should be matched exactly. Test with a small sample if the terms contain unusual punctuation, because the intended meaning of a pattern can be easy to overlook.
Blank entries are another common source of surprising results. In a contains formula, a blank term can turn into a criterion that matches many text cells. Keep the list range limited to populated cells, remove empty rows, or maintain a separate clean keyword list. Also check for terms that occur inside other words, repeated terms, and spelling variations; Excel only matches the characters actually present.
Common errors and practical troubleshooting
If the result is larger than the number of records, first check whether the formula is counting term hits rather than distinct rows. A row matching two terms contributes twice to SUM(COUNTIF(…)). Use BYROW or a helper column when the question is “How many cells contain at least one listed term?”
If Excel shows a formula error or an unexpected zero, verify the following:
- Confirm that the data range and list range point to the intended cells and do not accidentally include headings.
- Check for blank entries, duplicate keywords, leading or trailing spaces, and spelling differences.
- Remember that wildcard COUNTIF matching is case-insensitive and can match a term inside a longer word.
- Check whether the list contains * or ? characters that Excel may treat as wildcards.
- If a formula uses BYROW or LAMBDA and Excel does not recognize the function, try the helper-column method or confirm the Excel version.
For data imported from another system, extra spaces or nonprinting characters can also prevent an expected match. Compare the source text and list entry directly, and clean the data if needed before counting. Do not assume that two values that look identical on screen contain identical characters.
Frequently Asked Questions
Can COUNTIF search for multiple words from a list?
Yes. Combine COUNTIF with wildcards and SUM, as in the opening formula. It evaluates each list term and adds the match counts, so a cell containing several listed terms contributes more than once.
How do I count each cell only once if it contains any list item?
Use the BYROW and LAMBDA formula to test each cell against the whole list, or use a helper column that returns TRUE or FALSE for each row and count the TRUE results. Both approaches count matching rows rather than individual term hits.
Is COUNTIF with wildcards case-sensitive?
No. COUNTIF matching is case-insensitive. SEARCH is also case-insensitive. Use FIND in a suitable formula when uppercase and lowercase must be treated differently.
Does the formula match whole words only?
No. A wildcard contains search can match a term inside a longer string, such as apple inside pineapple. COUNTIF wildcards do not provide a general whole-word boundary rule; a whole-word requirement needs a more tailored method based on how the text is formatted.
Why is my result counting the same row more than once?
The SUM(COUNTIF(…)) method adds one result for each term found. If a row matches two terms, it contributes two. Use the distinct-cell formula or helper column if each row should count once.
Related
- Excel Conditional Formatting With Dates: A Practical Guide
- Excel Conditional Formatting for Date Ranges Explained
- Excel Conditional Formatting for Upcoming Dates
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Z Score Formula: Calculate and Interpret ResultsWith a value in A2, its mean in B2, and its standard deviation in C2, calculate its z…
- Excel FILTER Formula to Find Cells Containing TextTo return rows where a column contains particular text, combine Excel’s FILTER function with SEARCH: with records in…
- Excel Table Filter Button Greyed Out? Fixes That WorkIf Excel’s filter button is greyed out, first press Esc to leave cell-edit mode, ungroup any selected worksheets,…
- Excel Keep Duplicates: Remove Unique RowsTo keep every row whose value appears more than once, add a helper formula such as =COUNTIF($A$2:$A$8,A2)>1, fill…
- Divide in Excel: Formula and Keyboard TipsTo divide in Excel, type a forward slash (/) between the numbers or cell references in a formula,…
- Excel: How Many Days Are in a Month? Formula GuideA month can have 28, 29, 30, or 31 days, so Excel needs a year and month—or a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.