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.

I believe this is impossible without using a macro because the size of the output depends on the size of the input. Also your task doesn't look like a realistic final problem. So if this is really an [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem), you should describe your real problem. — SergGr, Feb 07 '18 at 13:36
Might also be worth looking at this answer (for google sheets) https://stackoverflow.com/questions/47922267/how-to-perform-iteration-over-excel-google-sheets-cells/47932221#47932221 . Although it shows combinations of strings it could be used for sums with slight modification. — Tom Sharpe, Feb 07 '18 at 22:51

TheMaster · Answer 1 · 2018-02-07T13:57:49.667

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

Technically this is a permutation of all pairs (e.g. `1+2` is considered *different* than `2+1`) rather than a combination of all pairs (e.g. `1+2` is considered the *same* as `2+1`). So you have twice as many results as intended. Still may fit the OP's needs, though. — ImaginaryHuman072889, Feb 07 '18 at 15:05

score 2 · Answer 2 · answered Feb 07 '18 at 15:19

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

Gary's Student · Answer 3 · 2018-02-07T14:55:02.423

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.

Unless I'm misunderstanding, the OP wants the sum of all pairs of numbers in a list, which would drastically reduce the number of outcomes here. That's just the way I interpreted the question though. — ImaginaryHuman072889, Feb 07 '18 at 15:06
@ImaginaryHuman072889 You may be correct..........I will work up an alternative answer........... — Gary's Student, Feb 07 '18 at 15:09

Sum all combinations Excel or Google Spreadsheets

3 Answers3

Linked