Quick, clean, and to the point

Count cells over 100 characters

Excel formula: Count cells over 100 characters
Generic formula 

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:


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:


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


Each TRUE corresponds to a cell that contains more than 100  characters. The N function converts these values to ones and zeros:


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

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

The final result is 3.

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.