Sum numbers in cell seperated by a comma

[FryGirl]

I have two methods to do this, but not quite working out for me yes. The UDF is great but I don't want to save this workbook with macros. The Evaluate method seems to have to be in the same row.

Is there a formula solution?

2, 3, 6, 8 = 19

Function Test(Rng As Range) As Variant
   Test =Evaluate(Application.Substitute(Rng, ",", "+"))
End Function

Ribbon > Formulas > Defined Names > Define Name

Name: MyArray

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

Click OK

Then enter the following formula in C8...

=SUM(MyArray)
​

 

[Gerald Higgins]

Is the input data ALWAYS in the same format, i.e. 4 numbers separated by comma space ?

If yes, you can use text functions to identify the numbers between the commas.

 

[FryGirl]

Hi Gerald,

The numbers could also look like 215, 283, 102, 186, 72, 8, 16

Seven numbers are the max.

 

[Gerald Higgins]

OK well if you want to go down the route of text functions, then personally I would use helper columns to break out your input data into the individual numbers.

For example

In B1
=LEFT(A1,FIND(",",A1)-1)+0

In C1
=LEN(B1)

In D1
=MID(A1,C1+3,100)

In E1
=LEFT(D1,FIND(",",D1)-1)+0
which is basically a repeat of what's in B1, and so on.

Then insert a formula to sum B1, E1, etc.

OR, for a different approach, how about Excel's Text to Columns function ?

 

[FryGirl]

Hi Gerald, text to columns will not work in this situation, but I found this CSE formula

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet3!F9,","," "),"-"," ")," ",REPT(" ",100)),ROW(INDIRECT("1:20"))*100-99,100),))/12

 

[tyija1995]

Depending on how the spreadsheet looks like you can separate the values as they are delimited by a comma.

You can do this with a formula: E.g. Lets say A1 contains "215, 283, 102, 186, 72, 8, 16"
Then in B1 you can use this and copy across to H1:

B1:

VALUE(TRIM(MID(SUBSTITUTE($A1,CHAR(44),REPT(CHAR(32),LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1))))

 

[Marcelo Branco]

Maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Text​

[/td][td]

Sum​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

215, 283, 102, 186, 72, 8, 16​

[/td][td]

882​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2, 3, 6, 8​

[/td][td]

19​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

5, 124​

[/td][td]

129​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

125​

[/td][td]

125​

[/td][/tr]
[/table]

B2 copied down
=SUM(INDEX(--("0"&TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",200)),1+(ROW($1:$7)-1)*200,200))),))

M.

 

[FryGirl]

Thank you Marcelo, this work great.

Thank you tyija1995, but I'm not wanting to separate the values.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.