# How to count cells that end with specific text in Excel

Put the asterisk before the text: `=COUNTIF(A2:A10,"*"&D1)` counts every cell that ends with the text in D1, such as an email domain.

**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 |
| --- | --- | --- | --- |
| Email |  | Ends with | @contoso.com |
| ana.lima@contoso.com |  | Addresses |  |
| ben.ortiz@fabrikam.com |  |  |  |
| cara.wu@contoso.com |  |  |  |
| dev.patel@contoso.com.au |  |  |  |
| eli.moss@gmail.com |  |  |  |
| fay.khan@CONTOSO.COM |  |  |  |
| gus.reyes@northwind.com |  |  |  |
| hana.sato@contoso.com |  |  |  |
| ivo.novak@sub.contoso.com |  |  |  |

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

Result: 4

## How it works

`"*"&D1` becomes `"*@contoso.com"`: anything, followed by the domain at the very end. "dev.patel@contoso.com.au" contains the domain but doesn't end with it, so it is left out.

Including the @ in D1 also keeps out "ivo.novak@sub.contoso.com", which ends in "contoso.com" but belongs to a subdomain. COUNTIF ignores case, so the address in capitals counts.

## Other ways

- `=SUMPRODUCT(--(RIGHT(A2:A10,LEN(D1))=D1))`: Compares the last characters directly; useful inside a larger SUMPRODUCT with other conditions.
- `=COUNTIF(A2:A10,"*"&D1&"*")`: Counts addresses that contain the domain anywhere. (Also counts dev.patel@contoso.com.au.)

## Common mistakes

- Trailing spaces stop the match: "cara.wu@contoso.com " does not end with ".com". TRIM the column, or count with `=SUMPRODUCT(--(RIGHT(TRIM(A2:A10),LEN(D1))=D1))`.
- Leaving out the @ counts subdomains and look-alike domains such as "notcontoso.com".

## Questions

**How do I count file names by extension?**

Use the extension as the ending: `=COUNTIF(A2:A20,"*.pdf")`.

**How do I list the matching cells instead of counting them?**

In Excel 365 or Google Sheets, use `=FILTER(A2:A10,RIGHT(A2:A10,LEN(D1))=D1)`.

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-end-with-specific-text