Count characters on a range of cells

You can use the LEN() function to get a count of characters from a value on a cell like this:

=LEN(A1)

But if you want to get a count of characters from a range of cells, if you try to do something like this =LEN(A1:B6) you will get an #VALUE error. The trick here is to use SUMPRODUCT() function to perform the count of characters using a formula like this:

=SUMPRODUCT(LEN(A1:B6))

You can see here, on this example, the result of this formula on a sample sheet.

COUNTCHARS

16 comentários:

Anonymous said...

Thank you so much. I've spent hours looking for this formula and finally found one that works. Most of the suggestions had SUM instead of SUMPRODUCT with no luck at all. Thanks again.

jppinto said...

Thanks for your comment. Glad that this little article helped you. jppinto

Anonymous said...

Hello,

How do I count the number of As or Bs in the above example?

thanks.

jppinto said...

To count the number of "A" on a single column, you can use this formula:

=SUMPRODUCT((MID(A1:A5,COLUMN(INDIRECT("A:"&CHAR(64+LEN(A1)))),1)={"A"})*1)

You can count "A" and "B" together like this:

=SUMPRODUCT((MID(A1:A5,COLUMN(INDIRECT("A:"&CHAR(64+LEN(A1)))),1)={"A";"B"})*1)

Anonymous said...

Thanks jppinto, I haven't been able to find sumproduct trick anywhere else, this is great!

Geoff said...

Thanks, worked perfectly! Saved me a lot of time / processing considering I have 26,000 rows :)

Anonymous said...

Exactly what I was looking for - thanks!

Waarith Parker said...

jppinto, you are the pro! thanx!!

Anonymous said...

How would I be able to count how many cells there that are contain characters in them? For example, it would calculate that row A has six cells that contain letters in them.

Bill Cufflin said...

Genius solution! Just helped me too. Great tip. How anyone ever figured that SUMPRODUCT & LEN could be paired to count characters in a range is beyond me - it never hints at it in the help files and seems counter-intuitive, but sure glad it works!

sneha sharma said...

Thank you for the help!!

Wocky said...

How can I count the max.number of characters in a range of cells?
e.g. From cel A to E, I have:
Foot, Chair, Bottle, Bed, Knife.
The result should be 6 because (Bottle=6 characters).

This would help me a lot.
Thanks!

Wocky said...
This comment has been removed by the author.
Anonymous said...

Thanks. This just helped me out. Much appreciated.

Anthony said...

I have a column with:
U
U
U
D
U
D
U
U
Is there a formula I can use to count the number of "U" and number of "D"?

Anonymous said...

Alternatively, you can use array formula by typing "=sum(len(A1:B6))" (without the braces) and then hitting CTRL + SHIFT + ENTER. The final formula will look like "{=SUM(LEN(A1:B6))}"

-Rajeev Sagar