## 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)`

**Note**: In this formula:

*is a column of blank cells beside your number list, and*

**B2:B10***is the number list that you use.*

**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.

- (1.) Click button to select the cell
- 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:

**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**

- 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 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:

*is the number list, and*

**A2:A10***is the total sum you want to get.*

**B2****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.

- 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:

**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**

- 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 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**

- 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!