sum numbers after a slash over multiple columns

[sushilchandi]

Hi ,

I need help adding numbers after a slash in multiple columns
For eg

1/1.01

65/21.11

100/100.12

45/1.00

Sum= 123.24

<tbody>
</tbody>

So i need the total of 1.01+21.11+100.12+1.00 = 123.24 in the last col

I can do this using vba but was wondering if this can be done WITHOUT vba.

Will appreciate all your help

 

[FormR]

Welcome to MrExcel, give this a try:

=SUMPRODUCT(MID(A1:C1,FIND("/",A1:C1)+1,255)+0)

Excel Workbook
	A	B	C	D
1	1/1.01	65/21.11	100/100.12	122.24
Sheet1

 

[sushilchandi]

Thanks .. This Works but if one column is empty the displays #value!
?

Welcome to MrExcel, give this a try:

=SUMPRODUCT(MID(A1:C1,FIND("/",A1:C1)+1,255)+0)

Sheet1

*	A	B	C	D
1	1/1.01	65/21.11	100/100.12	122.24

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:95px;"><col style="width:95px;"><col style="width:95px;"><col style="width:95px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D1 =SUMPRODUCT(MID(A1:C1,FIND("/",A1:C1)+1,255)+0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[FormR]

Maybe this array formula:

=SUM(IFERROR(MID(A1:C1,FIND("/",A1:C1)+1,255)+0,0)) confirmed with CTRL+SHIFT+ENTER

Excel Workbook
	A	B	C	D
1	1/1.01		100/100.12	101.13
Sheet1

 

[Firefly2012]

Here's a possible alternative:

Excel 2010
	A	B	C	D
1	1/1.01	65/21.11		22.12
Sheet1

Cell Formulas
Range	Formula
D1	=SUMPRODUCT(TRIM(MID(SUBSTITUTE(A1:C1&"/0","/",REPT(" ",99)),100,50))+0)

 

[sushilchandi]

Both work .. Thank You

Here's a possible alternative:

Excel 2010

	A	B	C	D
1	1/1.01	65/21.11		22.12

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D1 =SUMPRODUCT(TRIM(MID(SUBSTITUTE(A1:C1&"/0","/",REPT(" ",99)),100,50))+0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Firefly2012]

You're welcome