# How to sum if a cell contains one of several words in Excel

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)`.

**Excel:** `=SUMPRODUCT(((ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Expense | Amount |  | Word 1 | hotel |
| Hotel - Chicago | 412 |  | Word 2 | flight |
| Flight to Denver | 386 |  | Total |  |
| Team dinner | 214 |  |  |  |
| Hotel parking | 48 |  |  |  |
| Taxi to airport | 62 |  |  |  |
| Flight change fee | 75 |  |  |  |
| Conference badge | 299 |  |  |  |
| Hotel and flight bundle | 940 |  |  |  |

Formula in E3: `=SUMPRODUCT(((ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9)))>0)*B2:B9)`

Result: $1,861

## 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.

## Other ways

- `=SUM(FILTER(B2:B9,ISNUMBER(SEARCH(E1,A2:A9))+ISNUMBER(SEARCH(E2,A2:A9))))`: Excel 365 and Google Sheets: FILTER keeps every row where either search succeeds.
- `=SUMPRODUCT(REGEXMATCH(A2:A9,"(?i)"&E1&"|"&E2)*B2:B9)`: Google Sheets only: one regular expression with | for "or"; (?i) ignores case.
- `=SUMIF(A2:A9,"*"&E1&"*",B2:B9)+SUMIF(A2:A9,"*"&E2&"*",B2:B9)`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-if-cell-contains-one-of-several-words