If you need to know how many characters are in a cell then use the LEN function. What if you wanted to know how many characters were in a range?
That’s where Excel’s most flexible function comes in. SUMPRODUCT works well with ranges and it can process each cell in a range separately. This is just like an array formula, but without the drawbacks of array formulas. I’ll deal with arrays in future blog posts.
In the image below we want to calculate how many characters are in the range A2:A6.
To calculate how many characters are in cell A2 you would use
Which would return 3. (LEN is short for length).
To calculate the number of characters in a range you can use
In this case the answer is 22 and shown in the image below.
The SUMPRODUCT function processes each cell within the range to calculate its length and then adds up all the results to arrive at the count for the range.
I showed another use of the SUMPRODUCT function in a previous post here.
Note: spaces are characters too. LEN treats a space just like any other character and counts it.
I’ll demonstrate more advanced uses of the SUMPRODUCT function in future posts.