Sum all combinations Excel or Google Spreadsheets

Question

I need to create a spreadsheet where I will enter several (17) different numbers and have all possible combinations of sums between them. For example 1,2,3,4.....:

1+2
1+3
1+4
2+3
2+4
3+4
.....

It will be great if I can also know which two numbers the combinations come from.

Answer 1

If all you need are pairs of values, then use this simpler macro:

Sub PairsOnly()
    Dim Items(1 To 17) As Variant
    Dim i As Long, j As Long, k As Long
    Dim lower As Long, upper As Long
    lower = LBound(Items)
    upper = UBound(Items)
    k = 2

    For i = lower To upper
        Items(i) = Cells(1, i)
    Next i

    For i = lower To upper - 1
        For j = i + 1 To upper
            Cells(k, 1) = Items(i) & "," & Items(j)
            Cells(k, 2) = Items(i) + Items(j)
            k = k + 1
        Next j
    Next i
End Sub

Answer 2

If A2:A18 had the 17 numbers,

B1(heading):

=TRANSPOSE(A2:A18)

B2:

=ARRAYFORMULA(A2:A18+TRANSPOSE(A2:A18))

This will give a 17*17 table of SUM of all different combinations:

    1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17
1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18
2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19
3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20
4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21
5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22
6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23
7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24
8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25
9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26
10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27
11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28
12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29
13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30
14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31
15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32
16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33
17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34

Answer 3

This is for Excel using VBA

List your 17 values in A1 through Q1. Then run this macro:

Option Explicit

Sub ListSubsets()
    Dim Items(1 To 17) As Variant
    Dim CodeVector() As Integer
    Dim i As Long, kk As Long
    Dim lower As Long, upper As Long
    Dim NewSub As String
    Dim done As Boolean
    Dim OddStep As Boolean
    kk = 3
    OddStep = True
    lower = LBound(Items)
    upper = UBound(Items)
    For i = lower To upper
        Items(i) = Cells(1, i)
    Next i

    ReDim CodeVector(lower To upper) 'it starts all 0
    Application.ScreenUpdating = False
    Do Until done
        'Add a new subset according to current contents
        'of CodeVector

        NewSub = ""
        For i = lower To upper
            If CodeVector(i) = 1 Then
                If NewSub = "" Then
                    NewSub = "'=" & Items(i)
                Else
                    NewSub = NewSub & "+" & Items(i)
                End If
            End If
        Next i
        If NewSub = "" Then NewSub = "{}" 'empty set
        Cells(kk, 2) = NewSub
        Cells(kk, 3).Formula = Mid(NewSub, 2)
        kk = kk + 1
        'now update code vector
        If OddStep Then
            'just flip first bit
            CodeVector(lower) = 1 - CodeVector(lower)
        Else
            'first locate first 1
            i = lower
            Do While CodeVector(i) <> 1
                i = i + 1
            Loop
            'done if i = upper:
            If i = upper Then
                done = True
            Else
                'if not done then flip the *next* bit:
                i = i + 1
                CodeVector(i) = 1 - CodeVector(i)
            End If
        End If
        OddStep = Not OddStep 'toggles between even and odd steps
    Loop
    Application.ScreenUpdating = True
End Sub

The combinations will appear from B4 downwards and the associated sums in column D:

Adapted from John Coleman's code.

Post

NOTE:

This took about 4 minutes to run on my old laptop.