Help With Sum Formula

CARBOB

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

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,523
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,860
Thanks everyone, got many choices that worked. Saved them all in my Formula library.

Carbob
 

Watch MrExcel Video

Forum statistics

Threads
1,119,273
Messages
5,577,149
Members
412,770
Latest member
AlexiT4444
Top