Help With Sum Formula

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,866
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,261
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 31, 2005
Messages
5,828
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
Joined
Aug 22, 2005
Messages
101
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
Joined
Jun 6, 2005
Messages
1,866
Thanks everyone, got many choices that worked. Saved them all in my Formula library.

Carbob
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top