How to find the "value/sum" of numbers in a single cell

**bshelman** · 05-05-2008, 09:23 AM

If I have a cell with the number 123456789 in cell A1 I want to “add/count” the total/sum into another cell to show that (cell A1 is

1+2+3+4+5+6+7+8+9 (cell B1 is

= 45

HELP! Please...

**VBA Noob** · 05-05-2008, 09:34 AM

Try this array formula. Enter with Ctrl + Shift + enter

=SUM(VALUE(MID(A1,ROW(A1:OFFSET(A1,LEN(A1)-1,0)),1)))

VBA Noob

**NBVC** · 05-05-2008, 09:37 AM

Try:

=SUM(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0)

Confirmed with CTRL+SHIFT+ENTER not just ENTER.

**VBA Noob** · 05-05-2008, 09:39 AM

Or non array

=SUMPRODUCT(0+(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

VBA Noob

**martindwilson** · 05-05-2008, 09:41 AM

problem is thats ok if only single digits
but that could easily be 12+34+5+678+9 or any other combimation.
but for 9 single digits

=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)+MID(A1,4,1)+MID(A1,5,1)+MID(A1,6,1)+MID(A1,7,1)+MID(A1,8,1)+MID(A1,9,1)

will work but i wonder if there's a cleaner way

**martindwilson** · 05-05-2008, 09:44 AM

ah looking upwards there is!

**bshelman** · 05-05-2008, 10:26 AM

My goodness you guys are great! Thank you so very much for the help!