# Summing sequence of numbers seperated by comma within single cell

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

Thanks
Maria

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

3. ## 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. ## 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. ## Re: Summing sequence of numbers seperated by comma within single cell

=eval(substitute(a1,",","+")) should do it

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.``

12. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Summing sequence of numbers seperated by comma within single cell

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

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

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