# How to count cells that contain specific text in Excel

Use COUNTIF with wildcards: =COUNTIF(range,"*"&text&"*") counts every cell that contains the text anywhere.

**Excel:** `=COUNTIF(A2:A10,"*"&D1&"*")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Ticket subject |  | Word | refund |
| Refund not received |  | Tickets |  |
| Change delivery address |  |  |  |
| Partial refund request |  |  |  |
| Login problem after update |  |  |  |
| Where is my refund? |  |  |  |
| Cancel order and refund |  |  |  |
| Update payment card |  |  |  |
| Delivery late, want refund |  |  |  |
| Invoice copy needed |  |  |  |

Formula in D2: `=COUNTIF(A2:A10,"*"&D1&"*")`

Result: 5

## How it works

An asterisk stands for any characters, including none, so `"*"&D1&"*"` matches "refund" at the start, the middle or the end of the subject. COUNTIF ignores case, so "Refund" and "refund" both count.

## Other ways

- `=SUMPRODUCT(--ISNUMBER(SEARCH(D1,A2:A10)))`: Same result, and it can be combined with other array conditions. Use FIND instead of SEARCH for a case-sensitive count.
- `=COUNTIF(A2:A10,D1&"*")`: Counts only subjects that start with the word. (Only the subjects that begin with "refund".)

## Common mistakes

- Without the asterisks, `=COUNTIF(A2:A10,D1)` counts only cells that are exactly "refund".
- To count a literal * or ?, put a tilde before it: `"*~?*"` counts cells containing a question mark.
- COUNTIF counts cells, not occurrences. A subject with "refund" twice still counts once.

## Questions

**How do I count cells that contain one of several words?**

Add one COUNTIF per word, like `=COUNTIF(A2:A10,"*refund*")+COUNTIF(A2:A10,"*cancel*")`. A cell with both words is counted twice; use SUMPRODUCT with OR logic to avoid that.

**Is COUNTIF case-sensitive?**

No. For a case-sensitive count use `=SUMPRODUCT(--ISNUMBER(FIND(D1,A2:A10)))`.

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

Source: https://spreadsheetpractice.com/formulas/count-cells-that-contain-specific-text