Sum numbers in cell seperated by a comma

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,360
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Code:
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)
 
Last edited:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
Hi Gerald,

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

Seven numbers are the max.
 
Last edited:
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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:
Code:
VALUE(TRIM(MID(SUBSTITUTE($A1,CHAR(44),REPT(CHAR(32),LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1))))
 
Upvote 0
Maybe this...


A
B
1
Text​
Sum​
2
215, 283, 102, 186, 72, 8, 16​
882​
3
2, 3, 6, 8​
19​
4
5, 124​
129​
5
125​
125​

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

M.
 
Upvote 0
Thank you Marcelo, this work great.

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

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top