Exceljet

Quick, clean, and to the point

Count cells over 100 characters

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

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 C2 is:

=SUMPRODUCT(N(LEN(B5:B11)>100))

How this formula works

Working from the inside out, the LEN function runs on the range B5:B11. Because we give LEN multiple values, it returns multiple results in an array like this:

{127;78;43;112;59;72;154}

Which is evaluated against the logical expression >100. This results in an array of TRUE FALSE values:

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

The N function then converts these values to ones and zeros:

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

Finally, the array is processed by SUMPRODUCT, which returns 3:

=SUMPRODUCT({1;0;0;1;0;0;1})
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.