# How to Sum All Possible Combinations in Excel

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.

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

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

### Step 3: Use of Solver Add-in Feature

Now, we will solve this dataset.

âž¤ Click on Solver from the Data tab.

Doing so, the 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.

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

Note: this process shows only one combination.

Download Practice Workbook

You can download and practice from this Workbook.

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

## Related Articles

<< Go Back toÂ Excel COMBIN Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

4 Comments
1. Superb!Thank you, saved me ages of clicking through all the combinations!

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

Advanced Excel Exercises with Solutions PDF