How to count cells that contain one of several words in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0))Add a SEARCH test per word and count the cells where at least one is found: =SUMPRODUCT(--((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0)).
- Result on the example
- 6 in
E3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E3. Change any input and the result updates, or edit the formula to experiment.
How it works
SEARCH returns a position when it finds the word and an error when it doesn't, so ISNUMBER gives TRUE or FALSE per subject. Adding the two tests gives 0, 1 or 2 for each subject, and >0 keeps those with at least one word. SUMPRODUCT counts them.
Two subjects mention both refund and cancel. Adding two COUNTIFs would count them twice and give 8; this formula gives 6.
=SUMPRODUCT(--((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0))Result6
E1"refund"The value in E1.
A2:A109 rows × 1 col: "Refund not received", "Cancel my subscription", "Cancel order and refund", "Change delivery address", …A range: 9 cells in one column.
SEARCH(E1,A2:A10){9 rows × 1 col} 1, #VALUE!, 18, #VALUE!, …SEARCH: Position of text, not case-sensitive, allows wildcards.- find_text
E1"refund" - within_text
A2:A109 rows × 1 col: "Refund not received", "Cancel my subscription", "Cancel order and refund", "Change delivery address", …
- find_text
ISNUMBER(SEARCH(E1,A2:A10)){9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …ISNUMBER: TRUE if the value is a number.- value
SEARCH(E1,A2:A10){9 rows × 1 col} 1, #VALUE!, 18, #VALUE!, …
- value
E2"cancel"The value in E2.
SEARCH(E2,A2:A10){9 rows × 1 col} #VALUE!, 1, 1, #VALUE!, …SEARCH: Position of text, not case-sensitive, allows wildcards.- find_text
E2"cancel" - within_text
A2:A109 rows × 1 col: "Refund not received", "Cancel my subscription", "Cancel order and refund", "Change delivery address", …
- find_text
ISNUMBER(SEARCH(E2,A2:A10)){9 rows × 1 col} FALSE, TRUE, TRUE, FALSE, …ISNUMBER: TRUE if the value is a number.- value
SEARCH(E2,A2:A10){9 rows × 1 col} #VALUE!, 1, 1, #VALUE!, …
- value
ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)){9 rows × 1 col} 1, 1, 2, 0, …The + operator adds.
ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0{9 rows × 1 col} TRUE, TRUE, TRUE, FALSE, …The > operator tests greater than.
-((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0{9 rows × 1 col} -1, -1, -1, 0, …Makes the value negative.
--((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0{9 rows × 1 col} 1, 1, 1, 0, …Makes the value negative.
SUMPRODUCT(--((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0))6SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0{9 rows × 1 col} 1, 1, 1, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0)). We ran it in Google Sheets mode on the same data and it returns 6.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--REGEXMATCH(A2:A10,"(?i)"&E1&"|"&E2))Google Sheets | 6 | Google Sheets only: one regular expression with | for "or"; (?i) makes it ignore case. |
| =COUNTIF(A2:A10,"*"&E1&"*")+COUNTIF(A2:A10,"*"&E2&"*") | 8Different result | Quick, but only right when no cell contains both words. Returns 8: the two subjects with both words count twice. |
Common mistakes
- SEARCH finds the letters anywhere, so "cancel" also matches "cancelled". That is usually what you want; for whole words, see the page on counting whole-word matches.
- An empty word cell matches everything, because SEARCH finds "" at position 1. Make sure E1 and E2 are filled.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count cells that contain one of several words
E3, count the ticket subjects that contain the word in E1 or the word in E2, counting each ticket once.Questions
How do I check a longer list of words kept in a range?
In Excel 365: =SUM(--(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(E1:E5),A2:A10)),SEQUENCE(ROWS(E1:E5),1,1,0))>0)). MMULT adds up the matches per row; keep the list free of blanks.
How do I count cells that contain both words?
Use COUNTIFS with a wildcard condition per word: =COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*").
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.