Help With Sum Formula

[CARBOB]

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
	C	D	E	F
5	742		=SUM(1*(LEFT($C5,1)))&SUM(1*(MID($C5,2,1)))&SUM(1*(MID($C5,3,1)))	0
6	027			1
7	892			2
8	360			3
FILTERS

 

[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]

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]

In cell E5:

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

***EDIT:

Another way:

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

 

[CARBOB]

Thanks everyone, got many choices that worked. Saved them all in my Formula library.

Carbob