Let's say we have in excel the following numbers, each one in each own cell:
23-18-9-33-92-37
The goal is to sum all of the digits of all these 6 numbers.
23=2+3
18=1+8
9=9 or 0+9
33=3+3
92=9+2
37=3+7
And the final result should be:
2+3+1+8+0+9+3+3+9+2+3+7= 50
How you do that ?
Thank you !
Let say your numbers are in A1 to F1 try:
=SUMPRODUCT(--RIGHT(ROUNDDOWN($A1:$F1,ROW($A$1:$A$10)-10)/10^(11-ROW($A$1:$A$10)),1))
Edit: It return wrong result if has rounded numbers (10,20,30...).
But since martindwilson formula works fine I won't fix this.
Last edited by zbor; 06-24-2012 at 04:50 AM.
with the numbers in a1:f1
=SUM(VALUE(MID(A1&B1&C1&D1&E1&F1,ROW(A1:OFFSET(A1,LEN(A1&B1&C1&D1&E1&F1)-1,0)),1)))
array entered gives 50
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thank you o much everyone !
Sorry for reopening this thread again but I tried both formulas and none of them is working.
I put the file as attachment too.
Any help, please ?
Thank you !
Of course it doesn't work for first row where you don't have numbers?!
But for rest of them works:
=SUM(VALUE(MID(A2&B2&C2&D2&E2&F2,ROW(A$1:OFFSET(A$1,LEN(A2&B2&C2&D2&E2&F2)-1,0)),1)))
Look, I have used this formula and for the first draw it shows to me only 5 while the sum of digits of all 6 numbers is 45 and not 5. Check it out, please. It is as attachment.
Any help, please ?
Thank you !
COmfirm formula with ctrl+shift+enter (and not just enter)
Ohh, damn, it works man ! I love you ! Thank you ! :DOriginally Posted by zbor
there is a problem with the sumproduct approach
5 11 20 21 32 38 result is 30 when it should be 28
and the array one was missing some $
=SUM(VALUE(MID(A2&B2&C2&D2&E2&F2,ROW($A$1:OFFSET($A$1,LEN(A2&B2&C2&D2&E2&F2)-1,0)),1)))
I've add $ in your formula and edited my post with disclaimer it doesn't work due to rounded numbers 10,20,30...
But I won't fix it now since you formula works.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks