type sums("a1:c1") at cell d1

use this function


Function SUMS(R As Range)
Dim s As Double


For Each c In R
s = s + (Mid(c.Value, 1, Len(c.Value) - 1))
Next
SUMS = s

End Function