none
Sum of single-cell arrays RRS feed

  • 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