Adding multiple numbers in the same cell

[bmwbuyer2007]

Hi all,

Just wondering if it is possible to add numbers which are in the same cell, seperated by commas, for example

A B
1 Person 1 10, 10, 15
2 Person 2 10
3 Person 3 10, 10
4 Total 65

I know I could just put each number in a different cell, but for some reason the powers that be don't want to do this

Thanks

 

[MickG]

Hi,Give this a try,
Numbers in column "B"

Dim cl As Range, oSum, s As Integer, Tot As Double
For Each cl In Range(Range("b1"), Range("b" & Rows.Count).End(xlUp))
oSum = Split(cl, ",")
If InStr(cl, ",") > 0 Then
   For s = 0 To UBound(oSum)
        Tot = Tot + oSum(s)
      Next s
    Else
   Tot = Tot + cl
End If
Next cl
MsgBox Tot

Regards Mick

 

[pgc01]

Hi bmwbuyer2007

You can use this UDF:

Function SumComma(s As String)
    SumComma = Evaluate(Replace(s, ",", "+"))
End Function

Use it as any other worksheet function, like:

=SumComma(B1)