Count cells over 100 characters - KING OF EXCEL

Thursday, December 19, 2019

Count cells over 100 characters

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})
#evba #etipfree
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment