How to Sum All Possible Combinations in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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


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

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.

  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.

      ExcelDemy Team

Leave a reply

Advanced Excel Exercises with Solutions PDF