Step 1 – Initiating the Solver Add-in
Make sure your Solver Add-in feature is active.
- Go to File > Options.
- Select Add-Ins and click Solver Add-in in Inactive Application Add-ins.
- Click Go.
- In the Add-ins dialog box, click Solver Add-in.
- Click OK to install the add-in.
Step 2 – Using the SUMPRODUCT Function
This is the sample dataset.
There are different numbers in column B. Set the SUMPRODUCT formula In column D and the Result column will show combinations of numbers for a specific sum.
Enter the following formula in D5 and press ENTER.
=SUMPRODUCT(C5:C10,B5:B10)
D5 displays 0.
Read More: How to Generate All Possible Combinations of a Set of Numbers in Excel
Step 3 – Using the Solver Add-in Feature
- Click Solver in the Data tab.
In the Solver Parameters dialog box:
- In Set Objective, select $D$5 (the cell with the SUMPRODUCT formula).
- In To, select Value Of, and enter your sum value. Here, 100.
- In By Changing Variable Cells, select $C$5:$C$10.
- Click Add to add a constraint.
- Select $C$5:$C$10 in Cell Reference and bin from the drop-down box and click OK.
- In the Solver Parameters window, click Solve.
In the Solver Results window:
- Select Keep Solver Solution and click OK.
The result is displayed in binary. The 1’s are the numbers that will sum up to 100.
Step 4 – Changing the Sum Value
- Click Solver in the Data tab.
- In To, enter the sum as 150 and click Solve.
The combination of numbers that sum up to 150 is displayed.
Note: this process shows only one combination.
Download Practice Workbook
Download and practice.
Related Articles
- How to Get All Combinations of 2 Columns in Excel
- How to Apply All Combinations of 3 Columns in Excel
- How to Create All Combinations of 4 Columns in Excel
- How to Show All Combinations of 5 Columns in Excel
- How to Create All Combinations of 6 Columns in Excel
- How to Find Combinations Without Repetition in Excel
<< Go Back to Excel COMBIN Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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