Google Sheets COUNTIF by Text Length: Formulas & Tips

To count entries that are exactly five characters long in Google Sheets, use =SUMPRODUCT(--(LEN(A2:A100)=5)), assuming the values are in A2:A100. Change 5 to the length you need; LEN measures each cell’s content, while SUMPRODUCT totals the matches.

What “COUNTIF by length” means

COUNTIF counts cells that meet a condition, such as matching a word, number, or pattern. LEN returns the number of characters in a value. Because COUNTIF does not directly apply LEN to every cell in its range as part of its criteria, a formula that combines LEN with SUMPRODUCT is a reliable way to count cells based on character length.

This is useful when a spreadsheet contains values whose lengths matter: product codes, postal codes, short identifiers, initials, or form responses. For example, you might want to find five-character codes or check whether a column contains entries that exceed a character limit. The formula counts characters in cell contents; it does not count pixels, displayed width, or the number of words.

In Google Sheets, enter the formula in an empty cell and press Enter. The example uses a finite range rather than an entire column because a smaller range is easier to review and can calculate more efficiently. Expand A2:A100 to include your actual data, but keep the range consistent in related calculations.

Count cells with an exact character length

With text or values in A2:A100, this formula counts cells whose LEN result is exactly five:

=SUMPRODUCT(--(LEN(A2:A100)=5))

The comparison LEN(A2:A100)=5 produces a true-or-false result for each cell. The double unary operator, written as two minus signs, converts those results to 1s and 0s; SUMPRODUCT adds the 1s. If three cells have five characters, the formula returns 3. It returns 0 if no cells meet the test.

To make the target length adjustable, put the desired number in B1. With the data in A2:A100 and the target length in B1, use:

=SUMPRODUCT(--(LEN(A2:A100)=B1))

Now changing B1 from 5 to 8 changes the test without editing the formula. This setup is handy for a validation dashboard or a sheet where someone may need to check several different length limits.

LEN counts spaces that are part of the cell content. A value consisting of four letters followed by a space has a length of five, even if that final space is hard to see. If the intended rule is based on trimmed text, account for that separately rather than assuming the visible word is the whole cell value.

Use COUNTIF with question-mark wildcards

For text-only entries, COUNTIF can sometimes provide a shorter alternative. In A2:A100, this formula counts text values that match five single-character wildcards:

=COUNTIF(A2:A100,"?????")

In a COUNTIF criterion, each question mark represents one character. Five question marks therefore describe text with five characters. This approach is convenient when you need a fixed length and know the cells contain text, such as codes imported as text. It also makes the pattern visible directly in the criterion.

There are important limits. The wildcard pattern is a text-matching method, so it is not a general substitute for LEN when the range contains numeric values or mixed data. A cell containing the number 123 is not necessarily treated like the text value “123” for a wildcard criterion. If you need to count the length of numbers and text consistently, use the LEN and SUMPRODUCT formula instead.

Question mark and asterisk are special wildcard characters in COUNTIF criteria. An asterisk matches any sequence of characters, so it does not specify a precise length. If you need to search for a literal wildcard character rather than use its pattern meaning, Google Sheets uses a tilde to escape it. For general length checks, LEN avoids those wildcard details.

Count lengths above, below, or within a range

Length tests do not have to use an exact number. With entries in A2:A100, use this formula to count cells longer than five characters:

=SUMPRODUCT(--(LEN(A2:A100)>5))

To count cells with at least five characters, change the comparison to >=5. To count shorter values, use <5. Blank cells have a LEN result of zero, so they do not meet a test such as greater than five. This can make a length threshold more useful than a separate blank check.

To count values between four and eight characters, inclusive, with entries in A2:A100, use:

=SUMPRODUCT(--(LEN(A2:A100)>=4),--(LEN(A2:A100)<=8))

SUMPRODUCT multiplies corresponding array results, so a row contributes 1 only when both tests are true. The two tests must cover the same range. If you change A2:A100 in one part of the formula, change it in the other part too.

Spaces, numbers, blanks, and errors

A common surprise is that leading, trailing, and repeated spaces count as characters. If a user typed “pear ” with a space after the word, LEN counts that space. When spaces should not affect the rule, TRIM can remove leading and trailing spaces and reduce repeated spaces between words to single spaces. With values in A2:A100, this formula counts entries whose trimmed text is five characters long:

=SUMPRODUCT(--(LEN(TRIM(A2:A100))=5))

Be aware that TRIM changes the measurement rule: it ignores extra spacing rather than counting the exact cell content. That is often desirable for names or form responses, but may be wrong for identifiers where every character is meaningful.

LEN can also evaluate numeric values as text, but the result may not correspond to what a cell appears to show. Number formats can display decimals, dates, percentages, or leading zeros differently from the stored value. For example, a number formatted to display two decimal places may have a different underlying value from the text a reader sees. If length is a rule for codes that must preserve leading zeros, store or import them as text and check the displayed data before relying on a count.

Empty cells have length zero. A formula that returns an empty string may also look blank, so if the distinction between a truly empty cell and a formula-generated empty string matters, test that separately. Error values in the range can cause a length calculation to return an error rather than a count. Find and correct those source errors, or handle them deliberately with IFERROR if your counting rule says errors should be ignored.

Use a helper column for visible checks

A helper column can make length checks easier to audit, especially in a large or frequently updated sheet. With the first entry in A2, enter this formula in B2 and fill it down beside the source data:

=LEN(A2)

Column B then shows the character count for each row. To count how many rows have length five, with those results in B2:B100, use:

=COUNTIF(B2:B100,5)

This method separates the two tasks: LEN calculates each row’s length, and COUNTIF counts the results. It is easier to inspect when a total looks unexpected, because you can see which rows contribute. The trade-off is that it uses an extra column and the helper formulas must extend to new rows. In Google Sheets, you can also use an array formula approach, but the helper-column method is simple to maintain and explain to collaborators.

Choosing the right formula and troubleshooting

Use the method that matches the data and the way the sheet will be maintained:

LEN with SUMPRODUCT: Best for exact, minimum, maximum, or range-based length tests, especially when the column may contain both text and numbers.
COUNTIF with question marks: A concise option for a fixed number of characters when the entries are text and wildcard matching fits the requirement.
A helper column: Best when people need to inspect individual lengths, troubleshoot unusual rows, or reuse the length values in other calculations.

If the answer is unexpectedly low, check for spaces, nonprinting characters, numbers stored as numbers rather than text, or a range that stops before the last row. If it is unexpectedly high, verify that you are not counting a header, that the target length is correct, and that blank-looking cells do not contain spaces or formulas. Clicking a suspicious cell and inspecting its formula bar can reveal content that is not obvious from the grid.

Google Sheets runs in a web browser, and formulas are entered directly in cells. The core COUNTIF, LEN, and SUMPRODUCT functions also exist in Excel, but formula separators can depend on regional settings: some locales use semicolons instead of commas. Menu locations are not needed for these formulas. If a formula produces a parse error, check the spreadsheet’s locale and use the separator expected by that file.

Frequently Asked Questions

Can COUNTIF count cells with exactly a certain number of characters?
Yes, for text you can use question-mark wildcards, such as =COUNTIF(A2:A100,"?????") for five characters. For mixed text and numbers, or more flexible length tests, use LEN with SUMPRODUCT.

Does LEN count spaces in Google Sheets?
Yes. Leading, trailing, and internal spaces in a cell’s content count as characters. If extra spaces should be ignored, apply TRIM as part of the length calculation, while remembering that this changes what you are measuring.

How do I count cells with more than ten characters?
With the values in A2:A100, use =SUMPRODUCT(--(LEN(A2:A100)>10)). Replace the range and threshold with the ones that fit your sheet.

Will a length formula count numbers and leading zeros correctly?
LEN can measure numeric values, but a number stored as a numeric value may not preserve leading zeros. If a code such as 00427 must remain five characters, store it as text and verify that the cells contain the zeros rather than relying only on number formatting.

Why does my count differ from the number of visible characters?
Hidden spaces, formula-generated text, formatting, or special characters can make the cell content differ from what seems visible. LEN measures the value’s text representation, not the width of the displayed cell. Inspect the source cells and decide whether spaces should count before changing the formula.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.