# How to count cells that contain one of several words in Excel

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

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Ticket subject |  |  | Word 1 | refund |
| Refund not received |  |  | Word 2 | cancel |
| Cancel my subscription |  |  | Tickets |  |
| Cancel order and refund |  |  |  |  |
| Change delivery address |  |  |  |  |
| Partial refund request |  |  |  |  |
| Login problem after update |  |  |  |  |
| Please cancel the second item |  |  |  |  |
| Invoice copy needed |  |  |  |  |
| Refund for cancelled order |  |  |  |  |

Formula in E3: `=SUMPRODUCT(--((ISNUMBER(SEARCH(E1,A2:A10))+ISNUMBER(SEARCH(E2,A2:A10)))>0))`

Result: 6

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

## Other ways

- `=SUMPRODUCT(--REGEXMATCH(A2:A10,"(?i)"&E1&"|"&E2))`: Google Sheets only: one regular expression with | for "or"; (?i) makes it ignore case.
- `=COUNTIF(A2:A10,"*"&E1&"*")+COUNTIF(A2:A10,"*"&E2&"*")`: 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.

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

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/count-cells-that-contain-one-of-several-words