Sum the single cell values.

[motilulla]

Using Excel 2010

Hello,

My data looks like as below given example I got some formula from MrExcel but it does not seem to work, in the cell C6 hitting F2 I noticed that after a last coma there is one space also. Please could someone take a look and make it work to give a sum in range column “D” each of the columns “C” value are in a single cell....Expected sum are shown in the column "E"

Note: I want a formula or a macro which can be used in Excel 2000 also.

Sort By Range.xls
	B	C	D	E
3
4		Value In Single	Sum	Expected Sum
5		Cell		Values
6		2 , 5 , 11 , 15 , 44 ,	#¡VALOR!	77
7		6 , 20 , 34 , 36 , 38 ,	#¡VALOR!	134
8		16 , 30 , 35 , 42 , 43 ,	#¡VALOR!	166
9		4 , 10 , 22 , 42 , 49 ,	#¡VALOR!	127
10		19 , 21 , 36 , 39 , 44 ,	#¡VALOR!	159
11		16 , 17 , 18 , 35 , 41 ,	#¡VALOR!	127
12		1 , 7 , 37 , 41 , 48 ,	#¡VALOR!	134
13		19 , 29 , 40 , 46 , 50 ,	#¡VALOR!	184
14
Sheet2

Cell Formulas
Range	Formula
D6:D13	D6	=SUM(0+TRIM(MID(SUBSTITUTE(","&C6,",",REPT(" ",99)),ROW(INDEX($C$1:$C$20,1):INDEX($C$1:$C$20,(1+LEN(C6)-LEN(SUBSTITUTE(C6,",","")))))*99,99)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Regards,
Moti

 

[Fluff]

How about

=SUM(0+TRIM(MID(SUBSTITUTE(","&C6,",",REPT(" ",99)),ROW(INDEX($C$1:$C$20,1):INDEX($C$1:$C$20,(LEN(C6)-LEN(SUBSTITUTE(C6,",","")))))*99,99)))

 

[Phuoc]

Or try

Book1
	B	C	D	E
2
3
4		Value In Single	Sum	Expected Sum
5		Cell		Values
6		2 , 5 , 11 , 15 , 44 ,	77	77
7		6 , 20 , 34 , 36 , 38 ,	134	134
8		16 , 30 , 35 , 42 , 43 ,	166	166
9		4 , 10 , 22 , 42 , 49 ,	127	127
10		19 , 21 , 36 , 39 , 44 ,	159	159
11		16 , 17 , 18 , 35 , 41 ,	127	127
12		1 , 7 , 37 , 41 , 48 ,	134	134
13		19 , 29 , 40 , 46 , 50 ,	184	184
Sheet1

Cell Formulas
Range	Formula
D6:D13	D6	=SUM(0+(0&TRIM(MID(SUBSTITUTE(C6,",",REPT(" ",99)),ROW($C$1:$C$20)*99-98,99))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[motilulla]

How about

=SUM(0+TRIM(MID(SUBSTITUTE(","&C6,",",REPT(" ",99)),ROW(INDEX($C$1:$C$20,1):INDEX($C$1:$C$20,(LEN(C6)-LEN(SUBSTITUTE(C6,",","")))))*99,99)))

Hello Fluff, conformed your formula worked OK.

I appreciate your help. Wish you Good Luck!

Kind Regards,
Moti

 

[motilulla]

Or try

Book1
	B	C	D	E
2
3
4		Value In Single	Sum	Expected Sum
5		Cell		Values
6		2 , 5 , 11 , 15 , 44 ,	77	77
7		6 , 20 , 34 , 36 , 38 ,	134	134
8		16 , 30 , 35 , 42 , 43 ,	166	166
9		4 , 10 , 22 , 42 , 49 ,	127	127
10		19 , 21 , 36 , 39 , 44 ,	159	159
11		16 , 17 , 18 , 35 , 41 ,	127	127
12		1 , 7 , 37 , 41 , 48 ,	134	134
13		19 , 29 , 40 , 46 , 50 ,	184	184
Sheet1

Cell Formulas
Range	Formula
D6:D13	D6	=SUM(0+(0&TRIM(MID(SUBSTITUTE(C6,",",REPT(" ",99)),ROW($C$1:$C$20)*99-98,99))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hello Phuoc, I like your compact formula it results as requested.

I appreciate your help. Wish you Good Luck!

Kind Regards,
Moti

 

[Fluff]

Glad we could help & thanks for the feedback.