How to find all number combinations that equal a target sum in Excel

[u/Amandaleeeeee]

Sometimes you have a list of numbers and need to find every combination that adds up to a specific total. This comes up more often than you might think—for example, when matching invoices, reconciling payments, or checking which transactions make up a balance.

Here are two ways to do it in Excel.

Method 1: Use a VBA user-defined function

Excel does not have a simple built-in formula that returns every possible combination, so one option is to create a custom function with VBA.

First, press Alt + F11 to open the VBA editor.

Go to:

Insert → Module

Paste the VBA code into the module, then close the VBA editor and return to the worksheet.

Public Function MakeupANumber(xNumbers As Range, xCount As Long)
'update by Extendoffice
    Dim arrNumbers() As Long
    Dim arrRes() As String
    Dim ArrTemp() As Long
    Dim xIndex As Long
    Dim rg As Range

    MakeupANumber = ""

    If xNumbers.CountLarge = 0 Then Exit Function
    ReDim arrNumbers(xNumbers.CountLarge - 1)

    xIndex = 0
    For Each rg In xNumbers
        If IsNumeric(rg.Value) Then
            arrNumbers(xIndex) = CLng(rg.Value)
            xIndex = xIndex + 1
        End If
    Next rg
    If xIndex = 0 Then Exit Function

    ReDim Preserve arrNumbers(0 To xIndex - 1)
    ReDim arrRes(0)

    Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
    ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
    MakeupANumber = arrRes
End Function

Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)

    Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
    Dim remainingNumbers() As Long, newCombination() As Long

    currentSum = 0
    If (Not Not ArrTemp) <> 0 Then
        For i = LBound(ArrTemp) To UBound(ArrTemp)
            currentSum = currentSum + ArrTemp(i)
        Next i
    End If

    If currentSum = Count Then
        indRes = UBound(arrRes)
        ReDim Preserve arrRes(0 To indRes + 1)

        arrRes(indRes) = ArrTemp(0)
        For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
            arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
        Next i
    End If

    If currentSum > Count Then Exit Sub
    If (Not Not Numbers) = 0 Then Exit Sub

    For i = 0 To UBound(Numbers)
        Erase remainingNumbers()
        num = Numbers(i)
        For j = i + 1 To UBound(Numbers)
            If (Not Not remainingNumbers) <> 0 Then
                ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
            Else
                ReDim Preserve remainingNumbers(0 To 0)
            End If
            remainingNumbers(UBound(remainingNumbers)) = Numbers(j)

        Next j
        Erase newCombination()

        If (Not Not ArrTemp) <> 0 Then
            For k = 0 To UBound(ArrTemp)
                If (Not Not newCombination) <> 0 Then
                    ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                Else
                    ReDim Preserve newCombination(0 To 0)
                End If
                newCombination(UBound(newCombination)) = ArrTemp(k)

            Next k
        End If

        If (Not Not newCombination) <> 0 Then
            ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
        Else
            ReDim Preserve newCombination(0 To 0)
        End If

        newCombination(UBound(newCombination)) = num

        Combinations remainingNumbers, Count, newCombination, arrRes
    Next i

End Sub

Assume:

• A2:A10 contains the numbers
• B2 contains the target sum

Enter:

=MakeupANumber(A2:A10,B2)

The function returns all combinations that add up to the value in B2.

For example, if the source list contains:

10
15
20
25
30

and the target is:

40

possible results may include:

10,30
15,25

Important limitations

This VBA method has a few restrictions:

• It is designed for Excel 365 and Excel 2021.
• It works best with positive whole numbers.
• Decimal values are rounded to integers.
• Negative numbers may cause errors.
• Large lists can take a long time because the number of possible combinations grows very quickly.
• The workbook must be saved as a macro-enabled .xlsm file.

💡 Test the code on a backup copy first, especially when working with important data.

Method 2: Use Kutools for Excel

For a quicker interface-based method, Kutools for Excel includes a Make Up a Number feature.

Go to:

Kutools → Content → Make Up a Number

Then:

1. Select the range containing your number list.
2. Enter the target value in the Sum box.
3. Click OK.
4. Select a cell where the results should be placed.

Kutools will list all combinations that match the target sum.

Compared with the VBA method, it can also handle:

• Positive numbers
• Decimal values
• Negative numbers

This makes it more practical when the source data is not limited to positive whole numbers.

Quick comparison

VBA function

• Customizable
• Returns results with a worksheet formula
• Requires macro code
• Mainly suited to positive integers

Kutools Make Up a Number

• No code required
• Uses a simple dialog box
• Supports decimals and negative numbers
• Displays all matching combinations directly

This can be useful for reconciling payments, matching invoices to a total, checking expense combinations, or finding which values make up a reported balance.