Sum contents of a cell

[davez]

Hoping someone can help with this - I have varying values of differing lengths in individual cells that I need to sum eg -

A1 contains the value 198262456. I need to sum each number in that cell for a total of 43. I have been using the Mid function - =MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+ etc etc but the problem that I run into is that the cell character lengths can vary between 8 to 20, & I get #Value! errors when the MID formulas exceed the characters.

Any solutions?

 

[footoo]

Excel: 4 Ways to Sum Digits in a Number

Learn how to calculate the sum of digits using various methods in Excel.

www.listendata.com

 

[Rick Rothstein]

It would help if you included the version of Excel that you are using in your profile. Anyway, assuming you will not be inserting rows at the beginning of your worksheet, try this...

=SUM(0+(0&MID(A1,ROW(1:20),1)))

and if it doesn't work, then try this instead...

=SUMPRODUCT(0+(0&MID(A1,ROW(1:20),1)))

 

[davez]

It would help if you included the version of Excel that you are using in your profile. Anyway, assuming you will not be inserting rows at the beginning of your worksheet, try this...

=SUM(0+(0&MID(A1,ROW(1:20),1)))

and if it doesn't work, then try this instead...

=SUMPRODUCT(0+(0&MID(A1,ROW(1:20),1)))

Many thanks, the first suggestion works. My profile updated as well.

 

[Rick Rothstein]

Many thanks, the first suggestion works. My profile updated as well.

Actually, I had assumed you were not using xl365. Since you are, use this instead (and you can insert rows all you like)...

=SUM(0+MID(A1,SEQUENCE(LEN(A1)),1))