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.