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

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:

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

• 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.
This comment was minimized by the moderator on the site
I've gone through this twice and can't get it to work. I put the numbers in column A and nothing happens. I even put in two numbers that would add up just to make sure. Maybe it's cuz I need 14 lines instead of 10 and I edited the formula to include all the lines but all I get are zeros in the B column
This comment was minimized by the moderator on the site
What a challenge I was facing to find what group of numbers were used to get a particular total (charge to our account). Then I found this simple, yet well explained step-by-step instructions showing what formula to use and exactly what to do. It worked!!!! I am so, so grateful to have found this. Thank you so much for sharing and for the good service you are providing.
This comment was minimized by the moderator on the site
hola esta muy interesante la demostración del código para excel, pero si deseo que me combine números, con cierta cantidad de números por combinación, por ejemplo 4 números por combinación, y solo me arroje eso, para optimizar la memoria del procesador:
1,2,3,4
2,3,4,5
5,2,6,8

en este caso como sería el código
There are no comments posted here yet