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

ExcelDemyHi, 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.

RegardsExcelDemy Team