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


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:
Which is evaluated against the logical expression >100. This results in an array of TRUE FALSE values:
The N function then converts these values to ones and zeros:
Finally, the array is processed by SUMPRODUCT, which returns 3:

#evba #etipfree
📤You download App installed directly on the latest phone here :

Popular Posts