combinations with a sum total

[keith1]

Hi
I am trying to find a formula that will return the number of combinations in a given set of numbers with the the criteria being the sum of the numbers in the combination.
For example if the range is 1-20 and the number chosen is 5 there are 15504 combinations. I now want to find out how many of those 15504 combinations when added together give a value for example of 60.
Keith

 

[S.H.A.D.O.]

I know this is a really old request but I came across it while searching for something else.

I came up with this for anybody that is interested.
It also lists the actual combinations as well as telling you how many combinations there are in the message box.

Just change the Const MaxF As Integer = 20 if there are more than 20 numbers to be drawn from.
Obviously there is a limit of rows depending on what version of Excel is being used.

Option Explicit
Option Base 1
 
Const MinA As Integer = 1
Const MaxF As Integer = 20
 
Sub Sum_Total()
    Dim TotSum As Variant
    Dim A As Long, B As Long, C As Long, D As Long, E As Long
    Dim z As Long, lRow As Long
    Dim lCol As Range
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    Cells.Delete
    Cells(1, 1).Select
     
    TotSum = (InputBox("Please enter the required TOTAL SUM.", "PARAMETERS", 0))

    If IsNumeric(TotSum) Then
        TotSum = CInt(TotSum)
    Else
        Exit Sub
    End If

    For A = MinA To MaxF - 4
        For B = A + 1 To MaxF - 3
            For C = B + 1 To MaxF - 2
                For D = C + 1 To MaxF - 1
                    For E = D + 1 To MaxF
                        If TotSum = A + B + C + D + E Then
                            z = z + 1
                            Cells(z, 1) = A: Cells(z, 2) = B: Cells(z, 3) = C
                            Cells(z, 4) = D: Cells(z, 5) = E
                            ActiveCell.Offset(1, 0).Select
                        End If
                    Next E
                Next D
            Next C
        Next B
    Next A

    Set lCol = Columns(1)
    lRow = lCol.Cells(lCol.Rows.Count, "A").End(xlUp).Row

    MsgBox _
        vbCrLf & vbCrLf & _
        Format(lRow, "#,##0") & " combinations produced with the" & vbCrLf & _
        "TOTAL SUM of " & TotSum & ".", vbOKOnly + vbInformation, "RESULTS"

    Set lCol = Nothing
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Have fun!!!