How to count cells that end with specific text in Excel
Formula (Excel and Google Sheets)
=COUNTIF(A2:A10,"*"&D1)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.
- Result on the example
- 4 in
D2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
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.
=COUNTIF(A2:A10,"*"&D1)Result4
A2:A109 rows × 1 col: "ana.lima@contoso.com", "ben.ortiz@fabrikam.com", "cara.wu@contoso.com", "dev.patel@contoso.com.au", …A range: 9 cells in one column.
D1"@contoso.com"The value in D1.
"*"&D1"*@contoso.com"The & operator joins as text.
COUNTIF(A2:A10,"*"&D1)4COUNTIF: Counts cells that meet one condition.- range
A2:A109 rows × 1 col: "ana.lima@contoso.com", "ben.ortiz@fabrikam.com", "cara.wu@contoso.com", "dev.patel@contoso.com.au", … - criteria
"*"&D1"*@contoso.com"
- range
In Google Sheets
The same formula works in Google Sheets: =COUNTIF(A2:A10,"*"&D1). We ran it in Google Sheets mode on the same data and it returns 4.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(RIGHT(A2:A10,LEN(D1))=D1)) | 4 | Compares the last characters directly; useful inside a larger SUMPRODUCT with other conditions. |
| =COUNTIF(A2:A10,"*"&D1&"*") | 5Different result | 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".
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count cells that end with specific text
D2, count the email addresses in A2:A10 that end with the text in D1.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).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.