Skip to main content

How to find all combinations that equal a given sum in Excel?

Discovering all possible combinations of numbers within a list that add up to a specific sum is a challenge that many Excel users might encounter, whether for budgeting, planning, or data analysis purposes.

In this example, we have a list of numbers, and the objective is to identify which combinations from this list sum up to 480. The screenshot provided demonstrates that there are five possible groups of combinations that achieve this sum, including combinations like 300+120+60, 250+120+60+50, among others. In this article, we will explore various methods to pinpoint the specific combinations of numbers within a list that total a designated value in Excel.

Find a combination of numbers equal to a given sum with Solver function

Get all combinations of numbers equal to a given sum

Get all combinations of numbers that have a sum in a range with VBA code


Find cells combination that equal a given sum with Solver function

Diving into Excel to find cell combinations that add up to a specific number might seem daunting, but the Solver Add-in makes it a breeze. We'll walk you through the simple steps to set up Solver and find the right combination of cells, making what seemed like a complex task straightforward and doable.

Step 1: Enable Solver Add-in

  1. Please go to File > Options, in the Excel Options dialog box, click Add-Ins from the left pane, at then, click Go button. See screenshot:
  2. Then, the Add-Ins dialog appears, check the Solver Add-in option, and click OK to install this add-in successfully.

Step 2: Enter the formula

After activating the Solver add-in, you need to enter this formula into the cell B11:

=SUMPRODUCT(B2:B10,A2:A10)
Note: In this formula: B2:B10 is a column of blank cells beside your number list, and A2:A10 is the number list that you use.

Step 3: Configure and run Solver to get the result

  1. Click Data > Solver to go to the Solver Parameter dialog box, in the dialog, please do the following operations:
    • (1.) Click button to select the cell B11 where your formula is located from the Set Objective section;
    • (2.) Then in the To section, select Value Of, and enter your target value 480 as you need;
    • (3.) Under the By Changing Variable Cells section, please click button to select the cell range B2:B10 where will mark your corresponding numbers.
    • (4.) Then, click Add button.
  2. Then, an Add Constraint dialog box is displayed, click button to select the cell range B2:B10, and select bin from the drop down list. At last, click OK button. See screenshot:
  3. In the Solver Parameter dialog, click the Solve button, some minutes later, a Solver Results dialog box is popped out, and you can see the combination of cells which equal a given sum 480 are marked as 1 in column B. In the Solver Results dialog, please select Keep Solver Solution option, and click OK to exit the dialog. See screenshot:
Note: This method, however, has a limitation: it can only identify one combination of cells that add up to the specified sum, even if multiple valid combinations exist.

Get all combinations of numbers equal to a given sum

Exploring Excel's deeper capabilities lets you find every number combination that matches a specific sum, and it's easier than you might think. This section will show you two methods for finding all combinations of numbers equal to a given sum.

Get all combinations of numbers equal to a given sum with User Defined Function

To uncover every possible combination of numbers from a specific set that collectively reaches a given value, the custom function outlined below serves as an effective tool.

Step 1: Open the VBA module editor and copy the code

  1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, and paste the following code in the Module Window.
    VBA code: Get all combinations of numbers equal to a given sum
    Public Function MakeupANumber(xNumbers As Range, xCount As Long)
    'updateby 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
    

Step 2: Enter the custom formula to get the result

After pasting the code, close the code window to go back to the worksheet. Enter the following formula into a blank cell to output the result, and then press Enter key to get all combinations. See screenshot:

=MakeupANumber(A2:A10,B2)
Note: In this formula: A2:A10 is the number list, and B2 is the total sum you want to get.

Tip: If you want to list the combination results vertically in a column, please apply the following formula:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
The limitations of this method:
  • This custom function only works in Excel 365 and 2021.
  • This method is effective exclusively for positive numbers; decimal values are automatically rounded to the nearest integer, and negative numbers will result in errors.

Get all combinations of numbers equal to a given sum with a powerful feature

Given the limitations of the aforementioned function, we recommend a swift and comprehensive solution: Kutools for Excel's Make up a Number feature, which is compatible with any version of Excel. This alternative can effectively handle positive numbers, decimals, and negative numbers.With this feature, you can quickly get all combinations that equal to a given sum.

Tips: To apply this Make Up A Number feature, firstly, you should download Kutools for Excel, and then apply the feature quickly and easily.
  1. Click Kutools > Content > Make Up A Number, see screenshot:
  2. Then, in the Make up a number dialog box, please click button to select the number list that you want to use from the Data Source, and then enter the total number into the Sum textbox. Finally, click OK button, see screenshot:
  3. And then, a prompt box will pop out to remind you to select a cell to locate the result,  then, click OK, see screenshot:
  4. And now, all combinations that equal to that given number have been displayed as below screenshot shown:
Note: To apply this feature, please download and install Kutools for Excel first.

Get all combinations of numbers that have a sum in a range with VBA code

Sometimes, you might find yourself in a situation where you need to identify all possible combinations of numbers that collectively add up to a sum within a specific range. For instance, you might be seeking to find every possible grouping of numbers where the total falls between 470 and 480.

Discovering all possible combinations of numbers that sum up to a value within a specific range represents a fascinating and highly practical challenge in Excel. This section will introduce a VBA code for solving this task.

Step 1: Open the VBA module editor and copy the code

  1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
  2. Click Insert > Module, and paste the following code in the Module Window.
    VBA code: Get all combinations of numbers that sum up to a specific range
    Sub Getall_combinations()
    'Updateby Extendoffice
        Dim xNumbers As Variant
        Dim Output As Collection
        Dim rngSelection As Range
        Dim OutputCell As Range
        Dim LowLimit As Long, HiLimit As Long
        Dim i As Long, j As Long
        Dim TotalCombinations As Long
        Dim CombTotal As Double
        Set Output = New Collection
        On Error Resume Next
        Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
        If rngSelection Is Nothing Then
            MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
            Exit Sub
        End If
        On Error GoTo 0
        xNumbers = rngSelection.Value
        LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
        HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
        On Error Resume Next
        Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
        If OutputCell Is Nothing Then
            MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
            Exit Sub
        End If
        On Error GoTo 0
        TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
        For i = 1 To TotalCombinations - 1
            Dim tempArr() As Double
            ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
            CombTotal = 0
            Dim k As Long: k = 0
            
            For j = 1 To UBound(xNumbers, 1)
                If i And (2 ^ (j - 1)) Then
                    k = k + 1
                    tempArr(k) = xNumbers(j, 1)
                    CombTotal = CombTotal + xNumbers(j, 1)
                End If
            Next j
            If CombTotal >= LowLimit And CombTotal <= HiLimit Then
                ReDim Preserve tempArr(1 To k)
                Output.Add tempArr
            End If
        Next i
        Dim rowOffset As Long
        rowOffset = 0
        Dim item As Variant
        For Each item In Output
            For j = 1 To UBound(item)
                OutputCell.Offset(rowOffset, j - 1).Value = item(j)
            Next j
            rowOffset = rowOffset + 1
        Next item
    End Sub
    
    
    

Step 2: Execute the code

  1. After pasting the code, press F5 key to run this code, in the first popped out dialog, select the range of numbers you want to use,and click OK. See screenshot:
  2. In the second prompt box, select or type the low limit number, and click OK. See screenshot:
  3. In the third prompt box, select or type the high limit number, and click OK. See screenshot:
  4. In the last prompt box, select an output cell, which is where the results will start to be outputted. Then click OK. See screenshot:

Result

Now, each qualifying combination will be listed in consecutive rows in the worksheet, starting from the output cell you chose.

Excel gives you several ways to find groups of numbers that add up to a certain total, each method works differently, so you can choose one based on how familiar you are with Excel and what you need for your project. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials, please click here to access them. Thank you for reading, and we look forward to providing you with more helpful information in the future!


Related Articles:

  • List or generate all possible combinations
  • Let’s say, I have the following two columns of data, and now, I want to generate a list of all possible combinations based on the two lists of values as left screenshot shown. Maybe, you can list all the combinations one by one if there are few values, but, if there are several columns with multiple values needed to be listed the possible combinations, here are some quick tricks may help you to deal with this problem in Excel.
  • Generate all combinations of 3 or multiple columns
  • Supposing, I have 3 columns of data, now, I want to generate or list all combinations of the data in these 3 columns as below screenshot shown. Do you have any good methods for solving this task in Excel?
  • Generate a list of all possible 4 digits combinations
  • In some cases, we may need to generate a list of all possible 4 digits combinations of number 0 to 9, which means to generate a list of 0000, 0001, 0002…9999. To quickly solve the list task in Excel, I introduce some tricks for you.