How to Find Combinations that are Equal to the Given Sum in Excel

In today’s data-driven world, the ability to manipulate and analyze numbers effectively is crucial. Excel stands out as a powerful tool in this arena, particularly for solving complex numerical problems. One such problem is finding combinations of numbers that equal a given sum.

Whether you’re managing budgets, analyzing sales figures, or optimizing inventory levels, identifying these combinations can enhance decision-making and efficiency. In this guide, we aim to equip you with the techniques and formulas to Find Combinations that are Equal to the Given Sum in Excel.

Key Takeaways:

• Excel provides multiple methods like Solver, VBA, and SUMPRODUCT to find combinations of numbers that sum to a given target.
• Properly preparing and organizing your data in a clean column format is essential for accurate results.
• Solver is the easiest built-in tool for small to medium datasets to automatically identify the right number combinations.
• VBA is the best solution for large datasets where manual checking or Solver may not work efficiently.
• Real-world applications of this method include payment reconciliation, budget allocation, inventory packing, and fund distribution.

 

Setting Up Your Excel Sheet

Preparing Your Data

To effectively find combinations of numbers in Excel that sum to a specific value, it’s essential to properly prepare your data. Start by entering your list of numbers in a single column of your Excel sheet, clearly labeled to avoid confusion. For instance, you can use column B starting from cell B2. Ensure there are no empty cells within your data range, as gaps can lead to inaccuracies in calculations.

Next, designate a cell for the target sum—let’s say cell D2. Label this cell appropriately, such as “Target Amount,” to maintain clarity.

This setup simplifies the execution of formulas and scripts, allowing for easier adjustments in the future should your target sum change. By organizing your data in a structured manner, you lay a solid foundation for successful problem-solving.

Real Life Examples and Scenarios

Here are a few scenarios where I have personally used these methods:

• Payment Reconciliation – Finding which invoices combine to match a client’s payment.
• Budget Allocation – Selecting expense items that fit within a specific budget limit.
• Inventory Packing – Choosing item weights that sum up to a shipping box limit.
• Fund Distribution – Splitting donation amounts into combinations for beneficiaries.
• Exam Question Selection – Selecting questions from a question bank to match a target score or time limit.

 

Find Combinations that are Equal to the Given Sum

Method 1: Using Excel Solver (Best Built-In Method)

Excel Solver is my go-to solution for this problem. Solver is an Add-in that tries multiple combinations automatically to reach the desired sum. Let me explain the process step-by-step.

STEP 1: Prepare Your Data

List all your numbers in a column, for example, in B2:B7.

In C2:C7, create helper cells where Excel will decide whether to include a number or not (1 or 0).

STEP 2: Create the Target Sum Formula

In any cell, say D2, type:

=SUMPRODUCT(B2:B7, C2:C7)

This will calculate the sum based on selected numbers (1 means include, 0 means exclude).

STEP 3: Enable Solver

Go to File → Options → Add-ins → Select Excel Add-in → Click Go.

Check Solver Add-in → Press OK

STEP 4: Run Solver

Go to Data → Click Solver

• Set Objective: D2
• To: Value Of → Enter your target sum
• By Changing Variable Cells: C2:C7
• Add Constraints: C2:C7 = binary (This means only 0 or 1)

Click Solve.

STEP 5: Get Your Answer

Solver will return 1’s in C2:C7 wherever the number from B2:B7 is selected to reach the sum.

Method 2: Using VBA for Large Datasets

If your dataset is very large or if you need to run this task regularly, I also use a simple VBA macro to automate this. I use VBA when I need automation or when I want Excel to go through every possible combination quickly and check which numbers add up to my target sum.

The best part? Once I set the VBA code, I can run it anytime, and it will automatically find the correct combination for me — saving me from the headache of checking combinations manually.

Let me show you how I do it step-by-step.

STEP 1: Press Alt + F11 to open the VBA Editor.

STEP 2: Click Insert → Select Module.

STEP 3: Copy and paste the following VBA code into the Module:

Sub FindSumCombination()
Dim rng As Range
Dim targetSum As Double
Dim result As Boolean
Set rng = Range("B2:B7") 'Change as per your data
targetSum = 15500 'Change as per your required sum
result = FindCombination(rng, targetSum, 1, 0, "")
If result = False Then
MsgBox "No combination found!"
End If
End Sub
Function FindCombination(rng As Range, targetSum As Double, idx As Integer, currentSum As Double, selected As String) As Boolean
If currentSum = targetSum Then
MsgBox "Combination Found: " & selected
FindCombination = True
Exit Function
End If
If idx > rng.Count Or currentSum > targetSum Then
FindCombination = False
Exit Function
End If
If FindCombination(rng, targetSum, idx + 1, currentSum + rng.Cells(idx, 1).Value, selected & rng.Cells(idx, 1).Value & ", ") Then
FindCombination = True
Exit Function
End If
If FindCombination(rng, targetSum, idx + 1, currentSum, selected) Then
FindCombination = True
Exit Function
End If
FindCombination = False
End Function

STEP 4: Close the VBA Editor by pressing Alt + Q.

STEP 5: In Excel, press Alt + F8 to open the Macro dialog box. Select the macro named FindSumCombination → Click Run.

View the result in a message box showing the combination of numbers from your range that add up to the target sum.

 

Troubleshooting Common Issues

Issue 1: Solver Not Returning Solution

• Reason: No valid combination exists or the sum is not possible with the given numbers.
• Solution: Double-check the numbers and try smaller ranges or adjust the target sum slightly.

Issue 2: VBA Code Taking Too Long

• Reason: Large datasets create a huge number of possibilities.
• Solution: Optimize code, increase system resources, or use Solver for smaller cases.

Issue 3: Sum Not Matching Due to Decimal Precision

• Reason: Floating-point errors or hidden decimal values in Excel.
• Solution: Round numbers using ROUND() function or set decimal precision.

 

FAQs

1. Can I use Excel Solver for large datasets with hundreds of numbers?

Excel Solver is best suited for small to medium datasets because it works by testing different combinations within a limited processing capacity. When dealing with hundreds of numbers, Solver may take a lot of time to compute or may fail to find a solution due to system limitations. For larger datasets, it’s recommended to use VBA as it can handle a higher number of combinations through automation. Solver is still very useful for quick scenarios or smaller lists.

2. What happens if Solver doesn’t find any combination that matches the sum?

If Solver fails to return a solution, it simply means that there is no valid combination of numbers from your list that adds up to your specified target sum. This is a common scenario when the target sum is not practically achievable with the given data. I always recommend rechecking your numbers, verifying for data entry mistakes, or adjusting the target sum slightly. Trying a smaller range of data can also help find results faster.

3. Is there any risk of errors while using VBA for this task?

VBA is highly effective for finding combinations in large datasets, but there are a few things to be careful about. It can take a lot of processing time if the dataset is very large because VBA checks every possible combination. Also, decimal precision or incorrect range selection may cause inaccurate results. To avoid errors, it’s best to clean your data, round off numbers if required, and double-check your target sum before running the macro.

4. Can I find combinations of more than two or three numbers using these methods?

Yes, both Solver and VBA can find combinations involving any number of values — not just two or three. Solver tries multiple sets of numbers, but its speed and success depend on the dataset size. VBA, on the other hand, is designed to loop through every possible combination, regardless of how many numbers it takes to reach the target sum. This makes VBA more powerful for complex scenarios.

5. Will the SUMPRODUCT formula give me the exact combination automatically?

No, SUMPRODUCT is not designed to automatically find the correct combination. It only calculates the total of the numbers you select (using 1 to include or 0 to exclude) in the helper column. It is very useful for manual testing or when you already know which numbers to sum. But for automatically identifying the right combination from a large list, Solver or VBA is the best solution.