# How to count cells with a specific number of characters in Excel

Compare LEN of each cell with the length you want: `=SUMPRODUCT(--(LEN(A2:A11)=E1))`.

**Excel:** `=SUMPRODUCT(--(LEN(A2:A11)=E1))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Promo code |  |  | Length | 8 |
| SPRING26 |  |  | Codes |  |
| SAVE10 |  |  |  |  |
| WELCOME1 |  |  |  |  |
| VIP2026X |  |  |  |  |
| FREESHIP |  |  |  |  |
| BF2026 |  |  |  |  |
| SUMMERSALE |  |  |  |  |
| NEWUSER5 |  |  |  |  |
| HOLIDAY |  |  |  |  |
| LOYAL25 |  |  |  |  |

Formula in E2: `=SUMPRODUCT(--(LEN(A2:A11)=E1))`

Result: 5

## 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.

## Other ways

- `=COUNTIF(A2:A11,REPT("?",E1))`: REPT builds "????????", and each ? matches exactly one character. Works only on text cells, not numbers.
- `=SUMPRODUCT(--(LEN(A2:A11)>E1))`: 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.

## 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)`.

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-by-text-length