# sum numbers after a slash over multiple columns

#### sushilchandi

##### New Member
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.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Welcome to MrExcel, give this a try:

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

Excel Workbook
ABCD
11/1.0165/21.11100/100.12122.24
Sheet1

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>

 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

Maybe this array formula:

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

Excel Workbook
ABCD
11/1.01100/100.12101.13
Sheet1

Here's a possible alternative:

Excel 2010
ABCD
11/1.0165/21.1122.12
Sheet1
Cell Formulas
RangeFormula
D1=SUMPRODUCT(TRIM(MID(SUBSTITUTE(A1:C1&"/0","/",REPT(" ",99)),100,50))+0)

Both work .. Thank You

Here's a possible alternative:

Excel 2010
ABCD
11/1.0165/21.1122.12

</tbody>
Sheet1

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

</tbody>

<tbody>
</tbody>

You're welcome

Replies
6
Views
563
Replies
4
Views
519
Replies
1
Views
210
Replies
0
Views
114
Replies
7
Views
248

1,203,242
Messages
6,054,350
Members
444,718
Latest member
r0nster

### 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.

### Which adblocker are you using?

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

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