Cell Contains One of Many? Excel COUNTIF & SUMPRODUCT
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This article explains how to check whether a cell contains any one of several specified values or substrings in Excel, and how to count or flag such cells. You will learn the core formulas using COUNTIF, SUMPRODUCT, and ISNUMBER/SEARCH, along with practical examples and common pitfalls.
Formula
The core formula to test if a cell contains any of several substrings is:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(list, cell)))>0, "Yes", "No")
Where:
- cell – the text you are searching (e.g., A2).
- list – a range of cells (e.g., D1:D3) containing the substrings you want to look for.
- SEARCH – finds the position of a substring within the cell (case-insensitive, returns #VALUE! if not found).
- ISNUMBER – converts the result to TRUE if found, FALSE if not.
- — – coerces TRUE/FALSE to 1/0.
- SUMPRODUCT – adds up the 1s and 0s across the list.
- IF – returns “Yes” if the sum is greater than 0, otherwise “No”.
If you just want a count of how many of the listed items appear in the cell, use =SUMPRODUCT(--ISNUMBER(SEARCH(list, cell))).
Worked Example
Using the defaults from the calculator: cell contains “apple”, and the list is “apple”, “banana”, “cherry”.
- SEARCH(“apple”, “apple”) returns 1 → ISNUMBER → TRUE → 1
- SEARCH(“banana”, “apple”) returns #VALUE! → ISNUMBER → FALSE → 0
- SEARCH(“cherry”, “apple”) returns #VALUE! → ISNUMBER → FALSE → 0
- SUMPRODUCT adds 1+0+0 = 1
- IF(1>0, “Yes”, “No”) → “Yes”
The calculator returns 1, meaning “Yes”.
When to Use This Formula
This pattern is useful for data cleaning, categorization, and conditional formatting. For example:
- Flagging rows where a product description mentions any of several keywords (e.g., “sale”, “discount”, “clearance”).
- Checking if a file name contains any of a set of forbidden extensions.
- Grouping customer feedback by topic if the text mentions any of several topic words.
It works on a single cell at a time. To apply to a whole column, copy the formula down or use it inside a COUNTIFS or SUMPRODUCT over a range.
How to Read the Result
The result is a simple yes/no (or 1/0). If you use the count version, the number tells you how many of the list items appear in the cell. Note that SEARCH is case-insensitive, so “Apple” and “apple” are treated the same. If you need case-sensitive matching, replace SEARCH with FIND.
Common Mistakes and Pitfalls
- Using COUNTIF with wildcards incorrectly – COUNTIF can check for one substring at a time (e.g.,
=COUNTIF(A2,"*apple*")), but it cannot handle an array of substrings without extra tricks. The SEARCH/SUMPRODUCT approach is more flexible. - Forgetting to coerce TRUE/FALSE – If you use SUMPRODUCT without the double unary (–), the booleans may not sum correctly. Always include — or multiply by 1.
- Mixing up SEARCH and FIND – SEARCH is case-insensitive and allows wildcards; FIND is case-sensitive and does not allow wildcards. Choose based on your need.
- Not using absolute references for the list – When copying the formula down, the list range should be absolute (e.g., $D$1:$D$3) so it doesn’t shift.
- Partial matches – SEARCH finds substrings anywhere in the cell. If you need whole-word matches, you must add word boundaries or use a more complex formula.
Alternatives and Comparisons
Depending on your Excel version, you might consider:
- COUNTIF with wildcards – Simple for one substring, but clunky for many.
- FILTERXML – Can split text and compare, but requires XML syntax and is less readable.
- Power Query / Get & Transform – If you need to do this repeatedly on large data, Power Query offers a visual way to split and match.
- VBA – For very complex logic, a custom function might be easier, but it’s overkill for most cases.
For counting rows where a cell contains any of several items (not just one cell), you can combine with SUMPRODUCT over a range: =SUMPRODUCT(--(ISNUMBER(SEARCH("apple",A2:A100))+ISNUMBER(SEARCH("banana",A2:A100))>0)) – but note this counts rows where at least one of the items appears.
Limitations and Edge Cases
- Empty cells – If the cell is empty, SEARCH returns #VALUE!, so ISNUMBER gives FALSE. That’s fine.
- List with empty cells – If your list range includes blanks, SEARCH(“”, cell) returns 1 (because an empty string is found at position 1), which would incorrectly count as a match. Filter out blanks or use a dynamic array formula.
- Very long lists – SUMPRODUCT with SEARCH can become slow if the list has thousands of entries and you have thousands of rows. Consider using Power Query or a helper column with MATCH.
- Wildcards in list – SEARCH treats * and ? as wildcards. If your list contains literal asterisks, you must escape them with a tilde (~).
Frequently Asked Questions
How do I make it case-sensitive?
Replace SEARCH with FIND in the formula. FIND is case-sensitive and does not allow wildcards, so =IF(SUMPRODUCT(--ISNUMBER(FIND(list, cell)))>0, "Yes", "No") will match only exact case.
Can I use this with a list stored in a single cell?
Yes, if your list is in one cell separated by commas, you can split it using TEXTSPLIT (Excel 365) or a combination of TRIM and MID. For older Excel, you might need a helper column.
Why does my formula return #VALUE!?
This usually happens when the list range includes empty cells. SEARCH(“”, cell) returns 1, but if the list cell is truly blank, SEARCH looks for an empty string and returns 1, which is not an error. However, if you use FIND, empty cells cause #VALUE!. To avoid, filter out blanks or use IFERROR.
How do I count rows where any of the items appear?
Use an array formula like =SUMPRODUCT(--(ISNUMBER(SEARCH("apple",A2:A100))+ISNUMBER(SEARCH("banana",A2:A100))>0)). This counts each row once if at least one item is found.
Does this work in Google Sheets?
Yes, the same formulas work in Google Sheets. SEARCH, ISNUMBER, and SUMPRODUCT are all supported. The only difference is that in Google Sheets you might use ARRAYFORMULA instead of SUMPRODUCT in some cases.