How to count cells that start with specific text in Excel
Formula (Excel and Google Sheets)
=COUNTIF(A2:A12,D1&"*")Put the asterisk wildcard after the text: =COUNTIF(A2:A12,D1&"*") counts every cell that begins with the text in D1.
- Result on the example
- 5 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
The asterisk stands for any number of characters, so D1&"*" becomes "Senior*": "Senior" followed by anything. Because nothing comes before the text, the cell has to begin with it, which is why "Developer, Senior" is not counted.
COUNTIF ignores case, so "senior designer" counts, and so does "Seniority Review Lead", because it also starts with those six letters.
=COUNTIF(A2:A12,D1&"*")Result5
A2:A1211 rows × 1 col: "Senior Accountant", "Accountant", "Senior Data Analyst", "Data Analyst", …A range: 11 cells in one column.
D1"Senior"The value in D1.
D1&"*""Senior*"The & operator joins as text.
COUNTIF(A2:A12,D1&"*")5COUNTIF: Counts cells that meet one condition.- range
A2:A1211 rows × 1 col: "Senior Accountant", "Accountant", "Senior Data Analyst", "Data Analyst", … - criteria
D1&"*""Senior*"
- range
In Google Sheets
The same formula works in Google Sheets: =COUNTIF(A2:A12,D1&"*"). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--(LEFT(A2:A12,LEN(D1))=D1)) | 5 | Compares the first characters directly. Use it when you need to combine the test with other array conditions. |
| =SUMPRODUCT(--EXACT(LEFT(A2:A12,LEN(D1)),D1)) | 4Different result | A case-sensitive version: EXACT treats "senior" and "Senior" as different. Leaves out "senior designer", which starts with a lowercase s. |
Common mistakes
- "Senior*" also matches "Seniority". To match the whole first word, include the space:
=COUNTIF(A2:A12,D1&" *"). - Leading spaces break the match: " Senior Accountant" does not start with "Senior". Clean the column with TRIM first.
- Wildcards only work on text. A number like 4012 is not matched by
"40*"; see the page on counting numbers that contain digits.
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 start with specific text
D2, count the job titles in A2:A12 that start with the text in D1.Questions
How do I count cells that start with one of several words?
Add a COUNTIF per word: =COUNTIF(A2:A12,"Senior*")+COUNTIF(A2:A12,"Junior*"). A cell can only start one way, so nothing is counted twice.
How do I count cells that start with a letter?
Use the letter as the text: =COUNTIF(A2:A12,"S*").
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.