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

• By using User Defined Function
• By a smart feature – Kutools for Excel

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)

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:
   

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)

Tip: If you want to list the combination results vertically in a column, please apply the following formula:

=TRANSPOSE(MakeupANumber(A2:A10,B2))

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.

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:
   

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. Thank you for reading, and we look forward to providing you with more helpful information in the future!

Related Articles: