How to count cells with a specific number of characters in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(LEN(A2:A11)=E1))Compare LEN of each cell with the length you want: =SUMPRODUCT(--(LEN(A2:A11)=E1)).
- Result on the example
- 5 in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
LEN returns the number of characters in each code, and comparing those lengths with E1 gives TRUE for the codes that are exactly 8 characters long. SUMPRODUCT counts the TRUEs.
It works for numbers as well as text, because LEN reads the number as it would be typed.
=SUMPRODUCT(--(LEN(A2:A11)=E1))Result5
A2:A1110 rows × 1 col: "SPRING26", "SAVE10", "WELCOME1", "VIP2026X", …A range: 10 cells in one column.
LEN(A2:A11){10 rows × 1 col} 8, 6, 8, 8, …LEN: Number of characters.- text
A2:A1110 rows × 1 col: "SPRING26", "SAVE10", "WELCOME1", "VIP2026X", …
- text
E18The value in E1.
LEN(A2:A11)=E1{10 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
-(LEN(A2:A11)=E1{10 rows × 1 col} -1, 0, -1, -1, …Makes the value negative.
--(LEN(A2:A11)=E1{10 rows × 1 col} 1, 0, 1, 1, …Makes the value negative.
SUMPRODUCT(--(LEN(A2:A11)=E1))5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(LEN(A2:A11)=E1{10 rows × 1 col} 1, 0, 1, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(LEN(A2:A11)=E1)). 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 |
|---|---|---|
| =COUNTIF(A2:A11,REPT("?",E1)) | 5 | REPT builds "????????", and each ? matches exactly one character. Works only on text cells, not numbers. |
| =SUMPRODUCT(--(LEN(A2:A11)>E1)) | 1Different result | Counts codes longer than the limit, for checking a maximum length. Counts the one code longer than 8 characters. |
Common mistakes
- LEN counts spaces, including invisible trailing ones. Use
LEN(TRIM(A2:A11))if the data may have them. - For numbers, LEN counts the stored digits, not the formatting: 0.5 shown as 50% has length 3.
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 with a specific number of characters
E2, count the promo codes that are exactly as long as the number in E1.Questions
How do I count cells with more than N characters?
Change the comparison: =SUMPRODUCT(--(LEN(A2:A11)>E1)).
How do I find the length of a single cell?
Use =LEN(A2).
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.