Summing numbers separated by commas in a single cell

Question

_{Monday, September 29, 2014 7:58 PM}

I have seen some pretty outlandish code to simply add comma separated numbers in Excel (I am using 2013). What I am looking for should be very simple. I have a list of numbers separated by commas in a single cell (B8)that I simply need to add up to get the total (i.e., 3, 4, 5, 6...the sum would be 18 [shown in cell C8].) Pretty simple, huh? Is there a simple function to do this?  Thanks! 

All replies (4)

_{Monday, September 29, 2014 10:25 PM ✅Answered | 1 vote}

First, select cell C8.  With cell C8 selected, define the following name...

Ribbon > Formulas > Defined Names > Define Name

Name:  MyArray

Refers to:

=EVALUATE("{"&SUBSTITUTE($B8," ","")&"}")

Click OK

Then enter the following formula in C8...

=SUM(MyArray)

Note that the row reference in the formula for the defined name is a relative one , so the formula can be copied down the column, if needed.

Hope this helps!

Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

_{Thursday, April 13, 2017 12:46 PM}

First, select cell C8.  With cell C8 selected, define the following name...

Ribbon > Formulas > Defined Names > Define Name

Name:  MyArray

Refers to:

=EVALUATE("{"&SUBSTITUTE($B8," ","")&"}")

Click OK

Then enter the following formula in C8...

=SUM(MyArray)

Note that the row reference in the formula for the defined name is a relative one , so the formula can be copied down the column, if needed.

Hope this helps!

Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

Excellent solution sir

_{Tuesday, March 19, 2019 9:17 PM}

Domenic, this is such a lovely solution. I have a tricky part though that is causing it not to work, I think. 

The number values that are separated by commas, are not actually natively in the cell. Rather, they are alphanumeric codes that pull their value from a chart on another sheet via the VLOOKUP function. For example, I have a donation list and I have codes for each item a client donates. The value of each item is pulled from a table that shows the alphanumeric code for that item and the resulting monetary value. 

For example:

BEDSET1, COMPSYS1    may show in a single cell. 

Their values are $250 and $100, respectively. 

These values are found on a table on Sheet 2. 

I currently have a VLOOKUP function setup to pull the SINGLE value of each item if it's just one item listed in the cell. But I need to list more than one item per cell to keep all of the donors data in a single row. 

I'd like to be able to type the codes, separated by commas, in a single cell, and have a cell to the right show the total monetary value of those donations. Is that possible?

_{Thursday, September 26, 2019 4:36 PM}

Thanks a lot,,,this really helped me..