Add up Excel Cells

i have done the above but when ever i insert data the total simple said #Value! any idea. many thanks by the way

no sorry, it works as described, but is it possible to get a running total. for example 30 days in a month so instead of waiting until the last data is added on the 30th to find the total, could i, lets say on the 15th day also see a running total.

Andrew,

You've got me as the total is 487.

Instead of lots of complicated formulae why not just use Find to find all the commas in the string and Replace them with + signs. Then just put an = sign at the beginning and that's it. Ans =  487

Patrick

ps. By the way you can use Excel almost like a calculator. So for example if you type in =12+13+14+16 and press Enter you will get 54.

Patrick,

When he sees "125+1" he wants it treated as the value of 125 and not =125+1

Matt

Matt,

Thanks, I didn't read the question accurately.

Patrick

Andrew,

Not one to give up on possible simple solutions...

Instead of lots of complicated formulae why not just use Find & Replace in 2 stages:

1. Find all the + signs followed number and Replace them with nothing then
2. Find and replace all the commas in the string and Replace them with + signs
3. Then just put an = sign at the beginning and that's it. Ans =  482

Patrick

matt, wow super thanks very much.

Patrick,
Would have been a lot easier if the +1 factored into the equation :)

Andrew,
Glad to help!

ok how would you construct the formula if you want the +1 to be added to the result but displayed as for example 125+1, 20+1, 44 = 189+2 is this possible

If you wanted to include the 1s then you could use the VBA function below:

Usage:

If 125+1, 100, 52, 12, 41+3, 152+1 is in cell A1 then:

=calcs(A1) would return 487

Function calcs(ByVal target As Range) As String
Dim str() As String
Dim str2 As String
Dim i As Long
 
str() = Split(target, ", ")
str2 = ""
 
For i = 0 To UBound(str)
    str2 = IIf(i = 0, "=", "") & str2 & "+" & str(i)
Next i
 
calcs = Evaluate(str2)
 
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Good question.. I do have an answer for you but I'm hoping someone who is truly skilled in array formulae to chime in here, because this may get a little long.

This works as you described, but only if you would also want the sum of 125, 20, 44 to be "189+0":
=SUM(VALUE(IF(LEN(C6:AF6)=0,0,LEFT(C6:AF6,IF(ISERROR(FIND("+",C6:AF6)),LEN(C6:AF6),FIND("+",C6:AF6)-1)))))&"+"&SUM(VALUE(IF(LEN(C6:AF6)=0,0,IF(ISERROR(FIND("+",C6:AF6)),0,MID(C6:AF6,FIND("+",C6:AF6)+1,LEN(C6:AF6))))))

This works "fully", in that the above case would return 189 or 189+2 depending on if the +1s existed:
=SUM(VALUE(IF(LEN(C6:AF6)=0,0,LEFT(C6:AF6,IF(ISERROR(FIND("+",C6:AF6)),LEN(C6:AF6),FIND("+",C6:AF6)-1)))))&IF(SUM(VALUE(IF(LEN(C6:AF6)=0,0,IF(ISERROR(FIND("+",C6:AF6)),0,MID(C6:AF6,FIND("+",C6:AF6)+1,LEN(C6:AF6))))))=0,"","+"&SUM(VALUE(IF(LEN(C6:AF6)=0,0,IF(ISERROR(FIND("+",C6:AF6)),0,MID(C6:AF6,FIND("+",C6:AF6)+1,LEN(C6:AF6)))))))

That is quite a long formula though :)
Matt

sorry i meant for example.
125 +1
100
52
12
41+3
152+1

=482+5 (so it looks like this in the total colume)

See attached file...

Function calcs(ByVal target As Range) As Double
Dim str() As String
Dim str2 As String
Dim i As Long
 
str() = Split(target, ", ")
str2 = ""
 
For i = 0 To UBound(str)
    str2 = str2 & "+" & str(i)
Next i
 
calcs = Evaluate(str2)
 
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

please look at attach doc to see what i mean, thanks again

Andrew,

If none of the cells had a "+" in it, would you want it to have "+0" at the end or just left blank?

If left blank, use the second formula at http:#21474039 and if +0 then use the first (changing the range in either to C5:C64 of course)

Matt