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
- 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:
- 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
- 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.
- 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:
- 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
- Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
- Click Insert > Module, and paste the following code in the Module Window.
VBA code: Get all combinations of numbers equal to a given sumPublic 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)
=TRANSPOSE(MakeupANumber(A2:A10,B2))
- 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.
- Click Kutools > Content > Make Up A Number, see screenshot:
- 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:
- And then, a prompt box will pop out to remind you to select a cell to locate the result, then, click OK, see screenshot:
- 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
- Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
- 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 rangeSub 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
- 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:
- In the second prompt box, select or type the low limit number, and click OK. See screenshot:
- In the third prompt box, select or type the high limit number, and click OK. See screenshot:
- 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.
- List all possible combinations from single column
- If you want to return all possible combinations from single column data to get the result as below screenshot shown, do you have any quick ways for dealing with this task 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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!