Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


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.

How to Sum all Possible Combinations

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.

Sum all Possible Combinations

➤ Write the following formula in D5 and press ENTER.

=SUMPRODUCT(C5:C10,B5:B10)

The D5 cell should give 0.

Sum all Possible Combinations

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.

Sum all Possible Combinations

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

Sum all Possible Combinations

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.

Sum all Possible Combinations

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.


Related Articles

Sourav Kundu

Sourav Kundu

Bio: Hi, I am Sourav Kundu. I live in Adabor, Dhaka. I graduated in Naval Architecture and Marine Engineering from BUET. I am really excited to be a part of SOFTEKO family. I want to develop my research skill and find innovative solutions for the given problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo