# Sum cells with general format

#### bsneils

##### New Member
I have a group of cells in E2:E4 that are 144.00 in, 240.00 in, and 72.00 in and are formatted as general.

I get these values when I export data from an AutoCAD program to Excel and I want to sum these values to get the total length.

I want to sum the three values and have the result be the same (i.e., ###.## in). The number of values can go to 30, so I need a simple line of code, =SUM(???)

Any ideas would be great.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
=sum(e2:e4)

That does not work. Trust me, i tried.

Are you values numbers or text ?

Try isnumber(e2)

Try something like this (note, replace semicolon ; to comma , if your locale is not Dutch )

Excel Workbook
EF
2144,00 in144,00
3240,00 in240,00
472,00 in72,00
5456 in
Sheet3

Try

=SUM(LEFT(A1:A3,FIND(" ",A1:A3)-1)+0)&" in"

which is an array formula that must be entered using CTRL + Shift + Enter, not just Enter.

If entered correctly the formula will be surrounded by {} in the formula bar.

Still having trouble. this is my spreadsheet.

ITEM QTY PART NUMBER L
1 3 ibeam-1 144.00 in
2 2 ibeam-3 240.00 in
3 1 ibeam-2 72.00 in

This is what I get

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >144.00 in</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >240.00 in</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >72.00 in</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >456 in</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A4</td><td >{=SUM(LEFT<span style=' color:008000; '>(A1:A3,FIND<span style=' color:#0000ff; '>(" ",A1:A3)</span>-1)</span>+0)&" in"}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Hi, If your Numbers include "In" then try this.
Right click sheet tab, Vb Window appears.
On VB Toolbar , Click "Insert", "Module", New window appears, Paste entire code into window.
Close VB Window.
Use Function as :- =oSum(Select/Input your range here)
Code:
``````Function oSum(rng As Range) As String
Dim Dn As Range, Tot As Single
For Each Dn In rng
Tot = Tot + Split(Dn.Value, Chr(32))(0)
Next Dn
oSum = Tot & " In"
End Function``````
Regards Mick

Actually this is a better formula (confirmed with CTRL + Shift + Enter)

=TEXT(SUM(LEFT(A1:A3,FIND(" ",A1:A3)-1)+0),"0.00")&" in"

Replies
7
Views
127
Replies
4
Views
165
Replies
1
Views
86
Replies
4
Views
158
Replies
8
Views
243

1,203,487
Messages
6,055,713
Members
444,810
Latest member
ExcelMuch

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