Sourav Kundu
Excel is a handy tool to carry out different calculations. It’s often used for summation. This article is about how you can sum all possible combinations in Excel.
Download Practice Workbook
You can download and practice from this Workbook.
Steps to Sum All Possible Combinations in Excel
We will take a dataset of different numbers. The objective of this article is to find out the combination of numbers in Excel that will sum up a certain value. And for that, we will use the in-built Solver Add in feature and SUMPRODUCT function.
Step 1: Initiating Solver Add-in
First, you need to make sure that your Solver Add-in feature is active.
➤ Go to File > Options. From the Options dialog box, select Add-Ins and then click Solver Add-in from the Inactive Application Add-ins section.
➤ And click on Go.
An Add-in dialogue box will open.
➤ Click on the Solver Add-in option, and click OK to install this add-in.
Read More: How to Find All Combinations of 1 Column in Excel (2 Handy Ways)
Step 2: Inserting SUMPRODUCT Function
Now, let’s discuss the Excel dataset. We have different numbers in column B. In column D we will set the SUMPRODUCT formula and the Result column will show combinations of numbers for a specific sum.
➤ Write the following formula in D5 and press ENTER.
=SUMPRODUCT(C5:C10,B5:B10)
The D5 cell should give 0.
Read More: How to Use COMBIN Function in Excel (4 Examples)
Step 3: Use of Solver Add-in Feature
Now, we will solve this dataset.
➤ Click on Solver from the Data tab.
Doing so, Solver Parameters Dialogue box will open.
➤ In the Set Objective option, select the $D$5 cell in which the SUMPRODUCT formula is set.
➤ Then, in the To section, select Value Of, and enter your sum value. We took 100 for our sum value.
➤ After that, in the By Changing Variable Cells section, select the cell range $C$5:$C$10 where the combination mark will show up.
➤ And, click on Add to add constraint.
➤ Select $C$5:$C$10 in the Cell Reference and bin from the drop down box and click OK.
➤ Now, in the Solver Parameters window click on Solve.
A Solver Result window will open.
➤ Select Keep Solver Solution and click OK.
So, we get the result in binary. The 1’s are the numbers that will sum up to be 100.
Read More: How to Create All Combinations of 6 Columns in Excel (2 Methods)
Step 4: Changing Sum Value
Let’s change the sum.
➤ Click on Solver from the Data tab.
➤ And, from the To section insert the sum as 150 and press Solve.
Thereby, we get the combination of numbers that sum up to be 150.
Note: this process shows only one combination.
Read More: How to Find Combinations Without Repetition in Excel
Conclusion
So we have learned how to find possible combinations of numbers for a given sum. We hope you find this article informative. If there is any query or suggestion regarding the method, feel free to leave a comment.
Superb!Thank you, saved me ages of clicking through all the combinations!
Dear Charlotte,
You are most welcome.
Regards
ExcelDemy
Hi, how to know all the possibilities sums? i have 100 itens and my product sum have one or more valid result.
Hi there, Mr. Ronaldo!
Thanks for sharing your problem with us.
Excel’s Solver tool is designed for optimizing mathematical problems, not exhaustively finding all the possibilities sums. While Solver can search for combinations meeting a target, it might not guarantee all solutions, especially in complex cases. Solver’s algorithms might find suitable solutions, but they might not explore all combinations.
For finding all possible combinations leading to a fixed result, especially with many variables (like you have 100 items), Solver may not be the best option. Consider alternatives like custom Excel VBA macros or external programming languages like Python.
Regards
ExcelDemy Team