How to sum if a cell contains one of several words in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(((ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9)Add a SEARCH test per word and keep rows where any is found: =SUMPRODUCT(((ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9).
- Result on the example
- $1,861 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 turns each search into TRUE or FALSE. Adding the two tests gives 1 or 2 for a match, and >0 makes the bundle that mentions both words count once.
=SUMPRODUCT(((ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9)Result1861
E1"hotel"The value in E1.
A2:A98 rows × 1 col: "Hotel - Chicago", "Flight to Denver", "Team dinner", "Hotel parking", …A range: 8 cells in one column.
SEARCH(E1,A2:A9){8 rows × 1 col} 1, #VALUE!, #VALUE!, 1, …SEARCH: Position of text, not case-sensitive, allows wildcards.- find_text
E1"hotel" - within_text
A2:A98 rows × 1 col: "Hotel - Chicago", "Flight to Denver", "Team dinner", "Hotel parking", …
- find_text
ISNUMBER(SEARCH(E1,A2:A9)){8 rows × 1 col} TRUE, FALSE, FALSE, TRUE, …ISNUMBER: TRUE if the value is a number.- value
SEARCH(E1,A2:A9){8 rows × 1 col} 1, #VALUE!, #VALUE!, 1, …
- value
E2"flight"The value in E2.
SEARCH(E2,A2:A9){8 rows × 1 col} #VALUE!, 1, #VALUE!, #VALUE!, …SEARCH: Position of text, not case-sensitive, allows wildcards.- find_text
E2"flight" - within_text
A2:A98 rows × 1 col: "Hotel - Chicago", "Flight to Denver", "Team dinner", "Hotel parking", …
- find_text
ISNUMBER(SEARCH(E2,A2:A9)){8 rows × 1 col} FALSE, TRUE, FALSE, FALSE, …ISNUMBER: TRUE if the value is a number.- value
SEARCH(E2,A2:A9){8 rows × 1 col} #VALUE!, 1, #VALUE!, #VALUE!, …
- value
ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)){8 rows × 1 col} 1, 1, 0, 1, …The + operator adds.
ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0{8 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …The > operator tests greater than.
B2:B98 rows × 1 col: $412, $386, $214, $48, …A range: 8 cells in one column.
ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9{8 rows × 1 col} 412, 386, 0, 48, …The * operator multiplies.
SUMPRODUCT(((ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9)1861SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9{8 rows × 1 col} 412, 386, 0, 48, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(((ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9). We ran it in Google Sheets mode on the same data and it returns $1,861.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(FILTER(B2:B9,ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))) | $1,861 | Excel 365 and Google Sheets: FILTER keeps every row where either search succeeds. |
| =SUMPRODUCT(REGEXMATCH(A2:A9,"(?i)"&E1&"|"&E2)*B2:B9)Google Sheets | $1,861 | Google Sheets only: one regular expression with | for "or"; (?i) ignores case. |
| =SUMIF(A2:A9,"*"&E1&"*",B2:B9)+SUMIF(A2:A9,"*"&E2&"*",B2:B9) | $2,801Different result | Two SUMIFs added. Counts the hotel and flight bundle twice: 2,801. |
Common mistakes
- Adding one SUMIF per word double-counts rows that contain both words, like the bundle here.
- SEARCH ignores case; FIND doesn't. Use FIND only if "Hotel" and "hotel" should be different.
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.
Sum if a cell contains one of several words
E3, total the expenses whose name contains the word in E1 or the word in E2, counting each expense once.Questions
How do I check a longer list of words?
In Excel 365, put the words in E1:E5 and use =SUM(FILTER(B2:B9,MMULT(--ISNUMBER(SEARCH(TRANSPOSE(E1:E5),A2:A9)),SEQUENCE(5)^0))).
How do I require both words instead?
Multiply the tests: =SUMPRODUCT(ISNUMBER(SEARCH(E1,A2:A9))*ISNUMBER(SEARCH(E2,A2:A9))*B2:B9).
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.