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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I want to find specific characters or string of characters within a spreadsheet, count how many appear, and tally the result in some cells.
Solved by V. D. in 26 mins
In cell H7, enter a database function to count the total number of in-state early admissions. Use the range L9:M10 for the criteria range and C13 for the field.
Solved by C. C. in 19 mins
Hello, could I ask is there anyway to count a number of characters in a cell?
Solved by M. S. in 30 mins

Leave a Comment

avatar