+ Reply to Thread
Results 1 to 17 of 17

Summing sequence of numbers seperated by comma within single cell

  1. #1
    Registered User
    Join Date
    03-24-2009
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    5

    Summing sequence of numbers seperated by comma within single cell

    Hi all,

    I'm a basic excel user really and cannot find a function that will allow me to complete the following:

    CELL A1 includes the following sequence of numbers: 1,2,1,0,2,3
    I would like to know how to put a formula/function into CELL B1 that sums up the sequence of numbers in CELL A1 (i.e 1+2+1+0+2+3)

    Please help!!

    Thanks
    Maria

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Summing sequence of numbers seperated by comma within single cell

    Welcome to the forum, Mazb.

    This can be hacked, but it will be just that.

    Why not put the numbers in separate columns? If you have a lot of them, you can do Data > Text to columns to separate them, then use a conventional SUM formula.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-24-2009
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    5

    Re: Summing sequence of numbers seperated by comma within single cell

    Thanks..I know I can do it that way, but its a last resort really. It would work much better for me if I could do it in one cell.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Summing sequence of numbers seperated by comma within single cell

    Do the values to be summed ever exceed 9 ?

    If not then

    B1:
    =SUMPRODUCT((LEN($A1)-LEN(SUBSTITUTE($A1,{0,1,2,3,4,5,6,7,8,9},""))),{0,1,2,3,4,5,6,7,8,9})

    A UDF alternative which should cater for all values, eg:

    Please Login or Register  to view this content.
    Called from cell B1: =SUMSTRING(A1,",")

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Summing sequence of numbers seperated by comma within single cell

    =eval(substitute(a1,",","+")) should do it
    "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

  6. #6
    Registered User
    Join Date
    03-24-2009
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    5

    Re: Summing sequence of numbers seperated by comma within single cell

    That is brilliant. The following formula worked perfectly.

    =SUMPRODUCT((LEN($C5)-LEN(SUBSTITUTE($C5,{0,1,2,3,4,5,6,7,8,9},""))),{0,1,2,3,4,5,6,7,8,9})

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Summing sequence of numbers seperated by comma within single cell

    glad you like long formulas then!
    EVAL will work for any number not just single digits.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Summing sequence of numbers seperated by comma within single cell

    Martin, EVAL is part of morefunc.xll, ie not otherwise available.

    For your alternative approach to work you would need to install 3rd party add-in and embed / or distribute as required... which may/may not be viable for OP.

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Summing sequence of numbers seperated by comma within single cell

    yep and i dont know if it works on a mac either ! donkeyote 3 martin 0

  10. #10
    Forum Contributor
    Join Date
    03-22-2006
    Location
    UK
    MS-Off Ver
    Office 2013
    Posts
    275

    Re: Summing sequence of numbers seperated by comma within single cell

    In Excel 2003, I have used the following UDF (without morefunc.xll)

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.
    BUT, no idea if this will work with Excel 2004 for the Mac as OP uses.

    HTH ...spellbound

  11. #11
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Summing sequence of numbers seperated by comma within single cell

    If Cell A1 will always contain single-digits separated by commas (,)
    try this:

    Please Login or Register  to view this content.

    But, if A1 will always contain six single-digit numbers separated by commas
    try this:

    Please Login or Register  to view this content.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  12. #12
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Summing sequence of numbers seperated by comma within single cell

    Ron, I like the 2nd one and as you know I would normally defer judgement to you but in this instance I would argue the first is no improvement on that offered already (post 4) - in fact perhaps it is less effective given usage of INDIRECT, no ?

  13. #13
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: Summing sequence of numbers seperated by comma within single cell

    For any number of digits, ex.:

    1,23 , 456,2.4 ,-4

    we can also use a named formula.

    Ex. To sum the cell to the left

    - select B1
    - define the name:

    Name: SumCSV
    Refers to: =EVALUATE(SUBSTITUTE(Sheet1!A1,",","+"))

    This named formula evaluates the cell to the left. Ex., in B10:

    =SumCSV

    evaluates A10.

    For any number of digits, if you don't want to use vba, you can also use an array formula, extracting each number and summing them, but it will not be a small formula.

  14. #14
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Summing sequence of numbers seperated by comma within single cell

    As you may know, I'm not a big fan of INDIRECT solutions.
    INDIRECT isn't inherently evil, but when other people's large
    workbooks get sluggish, they're one of the first things I look for.

    In this particular case, I absentmindedly pulled an old formula from my stash.

    I should have posted my usual caveat that when used
    en masse, INDIRECT solutions degrade workbook performance.

    Thanks for questioning me on that one. As soon as I read your comment,
    I gave myself a newspaper over the snout!

  15. #15
    Registered User
    Join Date
    03-24-2009
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    5

    Re: Summing sequence of numbers seperated by comma within single cell

    I have a further query that I hope you can help with...

    I still have the cell with a sequence of numbers between 0 and 3, for example:

    (Cell A1) Player 1: 123102123122
    (Cell A2) Player 2: 121000212321

    I have 10 rows like this. For each player I want to count the number of times:
    - they scored '0' (cell B1)
    - they scored '1' (cell C1)
    - they scored '2' (cell D1)
    - they scored '3' (cell E1)

    So, is there a formula that allows me to do this?

    Thanks

  16. #16
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Summing sequence of numbers seperated by comma within single cell

    I'm assuming Player 1 does not appear in the string (else the Sumproduct would fail -- ie 1 would be included in the total)

    If so... use LEN

    B1: =LEN($A1)-LEN(SUBSTITUTE($A1,0,""))

    You can of course be creative re: incrementing integer

    B1: =LEN($A1)-LEN(SUBSTITUTE($A1,COLUMNS($B1:B1)-1,""))
    copied to C1:E1

  17. #17
    Registered User
    Join Date
    03-24-2009
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2004 for Mac
    Posts
    5

    Re: Summing sequence of numbers seperated by comma within single cell

    That works a treat..just what I wanted. Thanks lots!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1