Exceljet

Quick, clean, and to the point

Count cells that contain n characters

Excel formula: Count cells that contain n characters
Generic formula 
=SUMPRODUCT(--(LEN(range)=n))
Summary 

To count the number of cells that contain n characters, you can use the SUMPRODUCT function with the LEN function. In the example shown, the formula in E6 is:

=SUMPRODUCT(--(LEN(B5:B15)=E4))

The result is 4, since there are four cells in B5:B15 that contain five characters.

Explanation 

In this example, the goal is to count the number of cells in B5:B15 that contain a given number of characters, where the number of characters (n) is provided as a variable in cell E4. 

SUMPRODUCT with LEN

One way to solve this problem is to use theSUMPRODUCT function with the LEN function. In the example shown, the formula in E6 is:

=SUMPRODUCT(--(LEN(B5:B15)=E4))

Working from the inside out, the LEN function is used to get the length of each value in the range like this:

LEN(B5:B15)

Since the range B5:B15 contains 11 cells, LEN returns 11 results in an array like this:

{5;4;5;6;5;4;6;6;5;4;7}

Each number in the array is the length of a cell in B5:B15. This array is then compared to cell E4, which contains the number 5. The result is a new array containing 11 TRUE and FALSE values. To summarize:

=LEN(B5:B15)=E4
={5;4;5;6;5;4;6;6;5;4;7}=5
={TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

Each TRUE value corresponds to a cell in B5:B15 that contains 5 characters.

Next, a double-negative (--) is used to convert the TRUE and FALSE values to 1s and 0s, and the resulting array is delivered directly to the SUMPRODUCT function:

=SUMPRODUCT({1;0;1;0;1;0;0;0;1;0;0}) // returns 4

SUMPRODUCT returns the sum of the array, 4, as a final result. If a new number is entered in cell E4, the formula will recalculate and return a new result.

COUNTIF function

Another way to solve this problem is with the the COUNTIF function and the question mark (?) wildcardCOUNTIF supports three wildcards that can be used in the criteria argument: question mark (?), asterisk(*), or tilde (~). A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. In this example, we can use the question mark (?) wildcard to count cells that contain 5 characters like this:

=COUNTIF(B5:B15,"?????") // returns 4

The "?" symbol is a wildcard in Excel that means "match any single character", so this pattern will count cells that contain five characters. To adapt the formula above to use n from cell E4, we can add the REPT function like this:

=COUNTIF(B5:B15,REPT("?",E4))

The REPT function repeats the "?" five times inside COUNTIF, so the result is the same.

Note: One difference in the COUNTIF formula is that COUNTIF with "?" as a wildcard will only count characters in text values — cells that contain numeric values will not be counted. The SUMPRODUCT + LEN formula on the other hand will count characters of any kind, including numbers.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.