Exceljet

Quick, clean, and to the point

Count cells over n characters

Excel formula: Count cells over n characters
Generic formula 
=SUMPRODUCT(N(LEN(range)>n))
Summary 

To count cells that contain more than a certain number of characters, you can use a formula based on the SUMPRODUCT, LEN, and N functions. In the example shown, the formula in F6 is:

=SUMPRODUCT(N(LEN(B5:B15)>F4))

where n comes from cell F4, which contains 40. The result is 5, since there are 5 cells in B5:B15 that contain more than 40 characters.

Explanation 

In this example, the goal is to count the number of cells in a range that are over a certain number of characters in length, where the number (n) is provided as a variable in cell F4. This problem can be solved with the SUMPRODUCT and LEN functions like this:

=SUMPRODUCT(N(LEN(B5:B15)>F4)) // returns 5

The formula returns 5 since there are five cells in B5:B15 that contain more than 40 characters.

Reference calculation

The formula in C5, copied down, is based on the the LEN function:

=LEN(B5) // returns 25

This calculation is provided for reference only and is not used by the formula above. The counts in column C make it easy to quickly check results.

Checking length

Working from the inside out, the number of characters in each cell is calculated with the LEN function like this:

LEN(B5:B15)

The LEN function runs on the range B5:B15. Because we give LEN multiple values, it returns multiple results in an array like this:

{25;47;46;45;42;32;36;34;36;40;46}

Each number in the array is the length of a cell in B5:B15. This array is evaluated with the logical expression ">F4", which creates an array of TRUE FALSE values:

{FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}

Each TRUE corresponds to a cell that contains more than 40 characters, since cell F4 contains 40. To convert the TRUE and FALSE values to their numeric equivalents, we use the N function:

N({FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE})

Note: the double negative (--) is another way to convert TRUE/FALSE to 1/0.

The result is an array of 1s and 0s:

{0;1;1;1;1;0;0;0;0;0;1}

Counting results

This array is returned directly to the SUMPRODUCT function, which returns the sum of numbers in the array:

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

The final result is 5. Since n is provided as a variable in cell F4, it can be changed at any time and the formula will recalculate and return a new result.

COUNTIFS function

This is an example of a problem that can't be solved directly with the COUNTIFS function. This is because COUNTIFS requires a range and won't allow process an array like that returned by the LEN function above. However, if you don't mind using a helper column, you could use COUNTIFS on column C like this:

=COUNTIFS(C5:C15,">"&F4) // returns 5

The result is the same as the SUMPRODUCT formula above. Note the greater than operator (>) is enclosed in double quotes ("") and concatenated to F4.

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.