# Sum of single-cell arrays

• ### Question

• Hi!

I have {1,2;3,4} in *one* cell, and {9,7;8,10} in another cell. How can I sum those arrays? The result may be in one cell, or in a 2x2 range. Plain Excel, no VBA.

I appreciate any help here.

Thursday, February 14, 2013 7:29 PM

### All replies

• With those two string in cells A2 and A3.  For a single sum:

=VALUE(MID(A2,2,FIND(",",A2)-2))+VALUE(MID(A2,FIND(",",A2)+1,FIND(";",A2)-FIND(",",A2)-1))+VALUE(MID(A2,FIND(";",A2)+1,FIND(",",A2,FIND(";",A2))-FIND(";",A2)-1))+VALUE(MID(A2,FIND(",",A2,FIND(";",A2))+1,FIND("}",A2)-FIND(",",A2,FIND(";",A2))-1))+VALUE(MID(A3,2,FIND(",",A3)-2))+VALUE(MID(A3,FIND(",",A3)+1,FIND(";",A3)-FIND(",",A3)-1))+VALUE(MID(A3,FIND(";",A3)+1,FIND(",",A3,FIND(";",A3))-FIND(";",A3)-1))+VALUE(MID(A3,FIND(",",A3,FIND(";",A3))+1,FIND("}",A3)-FIND(",",A3,FIND(";",A3))-1))

For a 2x2 array:

=VALUE(MID(A2,2,FIND(",",A2)-2))+VALUE(MID(A3,2,FIND(",",A3)-2))
=VALUE(MID(A2,FIND(",",A2)+1,FIND(";",A2)-FIND(",",A2)-1))+VALUE(MID(A3,FIND(",",A3)+1,FIND(";",A3)-FIND(",",A3)-1))
=VALUE(MID(A2,FIND(";",A2)+1,FIND(",",A2,FIND(";",A2))-FIND(";",A2)-1))+VALUE(MID(A3,FIND(";",A3)+1,FIND(",",A3,FIND(";",A3))-FIND(";",A3)-1))
=VALUE(MID(A2,FIND(",",A2,FIND(";",A2))+1,FIND("}",A2)-FIND(",",A2,FIND(";",A2))-1))+VALUE(MID(A3,FIND(",",A3,FIND(";",A3))+1,FIND("}",A3)-FIND(",",A3,FIND(";",A3))-1))

And for completeness, VBA, which can handle arrays of any size:

Sum of all numbers returned to a single cell:

=SSum(A2:A3)

Array of Numbers returned to a MxN array of cells (in this case, 2x2), entered using Ctrl-Shift-Enter

=ASum(A2:A3)

with SSum ans ASum defined as:

Function SSum(r As Range) As Double
Dim c As Range
Dim v1 As Variant
Dim v2 As Variant
Dim i As Integer
Dim j As Integer
Dim s As String

For Each c In r
s = Replace(Replace(c.Value, "{", ""), "}", "")
v1 = Split(s, ";")
For i = LBound(v1) To UBound(v1)
v2 = Split(v1(i), ",")
For j = LBound(v2) To UBound(v2)
SSum = SSum + v2(j)
Next j
Next i
Next c
End Function

Function ASum(r As Range) As Variant
Dim c As Range
Dim v1 As Variant
Dim v2 As Variant
Dim i As Integer
Dim j As Integer
Dim s As String
Dim arr() As Double
ReDim arr(1 To 1, 1 To 1)
For Each c In r
s = Replace(Replace(c.Value, "{", ""), "}", "")
v1 = Split(s, ";")
For i = LBound(v1) To UBound(v1)
v2 = Split(v1(i), ",")
For j = LBound(v2) To UBound(v2)
If LBound(arr, 1) = UBound(arr, 1) Then
ReDim arr(LBound(v1) To UBound(v1), LBound(v2) To UBound(v2))
End If

arr(i, j) = arr(i, j) + v2(j)
Next j
Next i
Next c
ASum = arr
End Function

Thursday, February 14, 2013 9:42 PM