# Help With Sum Formula

#### CARBOB

##### Well-known Member
I have tried everything I know about Excel, and it's obvious,that's not much.
I'm trying to add the 3 digit number in Col "C", which comes from another worksheet produced by a macro. The formula, the way I have it setup has to be wrong, but I tried every way I know. The end product in Col "E" should be 13 and be a value so that I can use it in another formula. Thanks for all help.

Carbob
DOUBLESMIRRORSTOTALS_Nov30_B.xls
CDEF
5742=SUM(1*(LEFT(\$C5,1)))&SUM(1*(MID(\$C5,2,1)))&SUM(1*(MID(\$C5,3,1)))0
60271
78922
83603
FILTERS

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Joe4

I am not sure I follow your question, but if your are trying to add each part of your SUM together, you need to connect each piece with "+", not "&". "&" is a text concatenator, not an adding function.

#### NBVC

##### Well-known Member
You can try:

=SUM((LEFT(\$C5,1)),(MID(\$C5,2,1)),(MID(\$C5,3,1)))

or

=SUM(VALUE(LEFT(\$C5,1)),VALUE(MID(\$C5,2,1)),VALUE(MID(\$C5,3,1)))

#### Ronald Moore

##### Board Regular
In cell E5:

=SUM(MOD(INT(C5/10^{0,1,2}),10))

***EDIT:

Another way:

=SUM(VALUE(MID(C5,{1,2,3},1)))

#### CARBOB

##### Well-known Member
Thanks everyone, got many choices that worked. Saved them all in my Formula library.

Carbob

Replies
7
Views
338
Replies
1
Views
113
Replies
5
Views
476
Replies
7
Views
280
Replies
7
Views
191

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,633
Messages
5,832,780
Members
430,167
Latest member
Gogogoben

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