# Thread: Sum of numbers function (Excel 2000)

1. ## 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

2. ## 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

3. ## Re: Sum of numbers function (Excel 2000)

Wassim, Wassim -- Thought you'd be an array-formula champ by now (folks -- I know Wassim personally)
Try array-entering:
=SUM(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

4. ## 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

5. ## 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?

6. ## Re: Sum of numbers function (Excel 2000)

I followed all of that perfectly, but why does it have to be an array formula? --Sam

7. ## 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 array-entry that you tell it so.

8. ## 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

9. ## 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.