Sum of numbers function (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

Mario

WHAT THE HECK IS THAT “@sum” BIT DOWN THERE .

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 ‘/This is a simple Counter.
Dim iResult As Integer ‘/Holds the Results of all the additions.

For iCounter = 1 To Len(lNumber) ‘/Loop through the number
iResult = iResult + CInt(Mid(lNumber, iCounter, 1)) ‘/Add the Digits.
Next iCounter

AddDigitsOfNumber = iResult ‘/Assign the addition to the Function.
End Function

Reply | Quote

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))

Reply | Quote

Bob, sorry to always complain abiut your formulas, but this one really boggles my mind. 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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Mario

Yes I have sent you a workbook that has examples of how to do this thing. READ it all before you do anyting.

Glad to help.

Wassim

Reply | Quote