Results 1 to 9 of 9

20011119, 13:46 #1
 Join Date
 Aug 2001
 Location
 Cape Town, South Africa, South Africa
 Posts
 399
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sum of numbers function (Excel 2000)
Are there maybe a formula to add the individual numbers in a value together?
ie 10875 = 1+0+8+7+5 = 21
The only I can think of is writing VBA to change to text, split it up with Data text to columns and then changing back to numbers and then @sum. The range of values to do is from 1 to 20 000 000.
Thanks
Mario

20011119, 15:12 #2
 Join Date
 Jun 2001
 Location
 New York, New York, Lebanon
 Posts
 1,449
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Sum of numbers function (Excel 2000)
Mario
WHAT THE HECK IS THAT "@sum" BIT DOWN THERE <img src=/S/argue.gif border=0 alt=argue width=50 height=25>.
No its OK I was just thinking that Lotus is dead by now, thought one heck of a spreadsheet.
There are many ways to add the sum of the digits of a number. Here is a quick VBA one.
Function AddDigitsOfNumber(lNumber As Long) As Integer
Dim iCounter As Integer <font color=448800> '/This is a simple Counter. </font color=448800>
Dim iResult As Integer <font color=448800> '/Holds the Results of all the additions. </font color=448800>
For iCounter = 1 To Len(lNumber) <font color=448800> '/Loop through the number </font color=448800>
iResult = iResult + CInt(Mid(lNumber, iCounter, 1)) <font color=448800> '/Add the Digits. </font color=448800>
Next iCounter
AddDigitsOfNumber = iResult <font color=448800> '/Assign the addition to the Function. </font color=448800>
End Function<img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

20011119, 15:16 #3
 Join Date
 Jun 2001
 Posts
 91
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum of numbers function (Excel 2000)
Wassim, Wassim  Thought you'd be an arrayformula champ by now (folks  I know Wassim personally)
Try arrayentering:
=SUM(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

20011119, 17:18 #4
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Sum of numbers function (Excel 2000)
Bob, sorry to always complain abiut your formulas, but this one really boggles my mind. <IMG SRC=http://www.mtl.com/sam/images/headthrob.gif> I spent enough time to sort of see how it works, but how does the array formula know how/what to iterate? I thought it mearly iterated over the "source" range. Thanks! Sam
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20011119, 17:27 #5
 Join Date
 Jun 2001
 Posts
 91
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum of numbers function (Excel 2000)
Complain? Doesn't sound like a complaint!
Top understand the formula, let's assume A1 contains 10875.
=SUM(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
LEN(A1) is 5
"1:LEN(A1) is now "1:5"
INDIRECT("1:5") is the range 1:5
ROW(1:5) is then {1;2;3;4;5}
MID(A1,{1;2;3;4;5},1) is
{"1";"0";"8";"7";"5"}
1 * that is
{1;0;8;7;5}
SUM({1;0;8;7;5}) is 21.
Does that help?

20011119, 17:34 #6
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Sum of numbers function (Excel 2000)
I followed all of that perfectly, but why does it have to be an array formula? Sam
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20011119, 17:46 #7
 Join Date
 Jun 2001
 Posts
 91
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum of numbers function (Excel 2000)
Because of what I passed to the MID function. If you look at help, the MID function's second parameter is usually a number, NOT AN ARRAY OF NUMBERS. Like =MID(A1,5,3). If you pass an array to the function, like =MID(A1,{1,2,3},1) then Excel needs to know this and it's via the arrayentry that you tell it so.

20011120, 08:32 #8
 Join Date
 Aug 2001
 Location
 Cape Town, South Africa, South Africa
 Posts
 399
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sum of numbers function (Excel 2000)
Thanks for the quick response.
I've did it on the excel way. See attached file. Will you please use your code with all the comments from your "friends" to make mine work with your code (more correct)
Thanks
Mario

20011120, 16:12 #9
 Join Date
 Jun 2001
 Location
 New York, New York, Lebanon
 Posts
 1,449
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Sum of numbers function (Excel 2000)
Mario
Yes I have sent you a workbook that has examples of how to do this thing. <font color=red> READ </font color=red> it all before you do anyting.
Glad to help.
Wassim<img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>