Count cells over 100 characters in Excel
This tutorial shows how to Count cells over 100 characters in Excel using the example below;
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})