# How to count cells that contain both of two words in Excel

Use COUNTIFS with one wildcard condition per word on the same range: `=COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*")`.

**Excel:** `=COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*")` (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: `=COUNTIFS(A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*")`

Result: 2

## How it works

COUNTIFS counts a cell only when every condition is true, and both conditions can test the same range. The first keeps subjects containing "refund", the second those containing "cancel", so only subjects with both words are counted.

Order doesn't matter: "Cancel order and refund" and "Refund for cancelled order" both count.

## Other ways

- `=SUMPRODUCT(ISNUMBER(SEARCH(E1,A2:A10))*ISNUMBER(SEARCH(E2,A2:A10)))`: Multiplying the two tests is AND logic. Use FIND instead of SEARCH for a case-sensitive count.

## Common mistakes

- `"*refund*cancel*"` in one criteria only matches when refund comes before cancel. Separate conditions match either order.
- Partial words match: "cancel" is found in "cancelled". Add spaces around the word for a stricter test.

## Questions

**How do I count cells that contain one word but not another?**

Use a "<>" wildcard for the second: `=COUNTIFS(A2:A10,"*refund*",A2:A10,"<>*cancel*")`.

**How do I count cells with three words?**

Add a third pair: `,A2:A10,"*"&E3&"*"`.

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-two-words