Count cells over 100 characters in Excel

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;;;1;;;1})