Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    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

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

  3. #3
    Star Lounger
    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 array-formula champ by now (folks -- I know Wassim personally)
    Try array-entering:
    =SUM(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>

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

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>

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

  8. #8
    3 Star Lounger
    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
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    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
    Attached Files Attached Files
    <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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •