< Go Back

Count total characters in a range

How to Count Total Characters in a Range

To count total characters in a range can be challenging particularly while dealing with large data. However, with MS Excel, you can make the counting pretty easy. You just apply is a formula that uses SUMPRODUCT AND LEN functions.  Here’s how the formula looks.

Generic Formula

=SUMPRODUCT (LEN(rng)

…..where “rng” is the cell of interest while “LEN” is the formula that will count the characters before the total summations are carried out.

Example

We have got cells in the example that contain text. We want to get the count of total characters in a range using the formula discussed above. Check how we’ve done it:

Figure 1. Example 1 of SUMPRODUCT/ LEN Function

The formula of the in the highlights cell (C11) is:

=SUMPRODUCT(LEN(B5:B9))

How does the Formula Work

LEN is the first function to be evaluated. It calculates all characters in each of the five cell i.e. between B5 and B9. The calculation includes space characters in all cell. The result from LEN will, therefore, be an array of 5 numbers. The SUMPRODUCT then adds or sums up the five numbers in the array to return the final total. That is how a total of 98 is realized.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar