Find all number combinations that equal a specific sum in Excel (2023)

Find a combination of numbers that equals a specific sum with Excel Solver

Unfortunately, none of Excel's built-in functions can help you identify the numbers that add up to a specific total. Fortunately, Excel offers a special add-in for solving linear programming problems. The Solver add-in is included in all versions of Excel, but it is not enabled by default. If you are not familiar with this tool, here is an excellent article about it.How to Add and Use Solvers in Excel.

With the Solver add-in activated in your Excel, continue with the following steps:

1. Build the model.

   To do this, enter your series of numbers in a column (in our case A3:A12) and add an empty column for results (B3:B12) to the right of your numbers. In a separate cell (B13), type aSUMPRODUCTFormula similar to this:

   =SUMAPRODUKTO(A3:A12, B3:B12)

2. Run the solver plugin.

   About itDataguide in whichTo analyzegroup, click onresolvingI like.

3. Define the problem for solvers.

   insideSolver Parameterdialog box, define the target cells and variables:

   • insideset a targetenter the formula cell address (B13).
   • insideAsection, selectvalue ofand enter the desired sum value (50 in this example).
   • insidechange variable cellsSelect the range to be filled with results (B3:B12).
   
4. Add restrictions.

   To indicate restrictions, i. H. click on restrictions or conditions that must be metAdd toButton. insideadd restrictiondialog box, select the resulting range (B3:B12) and choosepaper basketin the drop-down list. The restriction is automatically set toSense. When finished, click OK.

5. To solve the problem.

   When he is brought backSolver ParameterPop-up window, review your settings and clickResolverI like.

   A few seconds (or minutes) afterSolver resultsdialog box appears. If successful, select thekeep solverand click OK to exit the dialog box.

   See Also

   Excel LOCATION and SEARCH FUNCTIONS with example formulasHow to use the search function of Excel

As a result, a 1 is inserted next to the numbers that add up to the specified sum. It's not an easy solution, but it's the best Excel can do.

For display, I've highlighted the cells that add up to the desired sum in light green:

restriction: Excel Solver can find at most one combination of numbers that equals a given sum.

Find all combinations that equal a given sum with custom functions

To get all possible combinations of a given set of numbers that add up to a given value, you can use the custom function below. If you're new to UDFs, you'll find a lot of useful information in this guide:How to Create Custom Custom Functions in Excel.

How this feature works

the main functionFindSumCombinations, calls some child functions that implement smaller subtasks. the named functionSumUpRecursiveCombinationsexecutes the main algorithm, which finds all possible sums in the specified range and filters out those that meet the objective. HeArrayToStringThe function controls the shape of the output strings. Three more functions (sum matrix,add to matrix, jcopy array) are responsible for processing intermediate arrays: every time we create a temporary array, we add an element from the source array and check if the target sum has been reached. The core algorithm is adoptedeste Hilo de StackoverflowThanks guys for sharing!

Syntax

From the user's point of view, the syntax of our custom function is as simple as:

FindSumCombinations(rango, suma)

Wo:

• Areais the range of numbers to test.
• additiveis the total target.

To use!The custom function only works in Dynamic Array Excel 365 and 2021.

To use the FindSumCombinations function:

1. Paste the code above into a code module in your workbook and save it as a macro-enabled workbook (.xlsm). Step by step instructions areHere.
2. In any blank cell, type aFindSumCombinationsformula and press the buttonTo enterButton. Make sure there are enough empty cells on the right side to generate all combinations. otherwise, the formula returns a#GAME error.

For example, to find all possible combinations of numbers in the range A6:A15 that are equal to the sum in A3, the formula is:

=FindSumCombinations(A6:A15, A3)

like everyonedynamic array function, enter the formula in just one cell (C6 in the image below) and enter the results in as many cells as needed. By default, comma-separated strings are displayed on one line:

To return results in a column, include the custom functionTRANSPONDERlike this:

=TRANSPOSE(FindSumCombinations(A6:A15, A3))

To output the strings as an array enclosed in braces, modify the ArrayToString function as follows:

The results will look something like this:

restriction- This custom function only works in Dynamic Array Excel 365 and Excel 2021.

Get all combinations equal to specified sum with VBA macro

The user-defined function described above returns combinations of numbers as strings. If you prefer to have each number in a separate cell, this macro will come in handy. The code is written by another Excel expert.Alejandro Trifuntowwhich actively helped users of this blog to solve various Excel problems.

Start by adding the following code to your workbook. For detailed steps, seeHow to Paste VBA Code in Excel.

Then create a UserForm with the following layout and properties:

Advice.Instead of creating the form from scratch, you can download our sample workbook at the end of this post and copy the code and UserForm1 module from there (see howCopy a Macro to Another Workbook). To make the macro available in all your workbooks, copy it to thepersonal macro book.

Once the code and form are in place, pressAlt+F8and run itSearch all combinationsMacro:

In the form that appears, define the following:

• The source number range (A4:A13)
• The target sum (50)
• The top left cell of the target range (C4).

When specifying the top left cell of the output range, make sure there are at leastan empty line above(for headers) and enoughblank cells down and to the right. If there are not enough blank cells, the existing data will be overwritten.

Upon clicking OK, the output will be generated as shown in the screenshot below:

• In C4:C6 you have the combinations of numbers as values ​​separated by commas.
• Columns F, G, and H contain the same number combinations, with each number in a separate cell.
• On D4 you have the target sum.

This form of output makes it easy to check the result: just enter the SUM formula in cell F13, drag it across two more cells, and you'll see that each combination of numbers produces the specified value (50). .

Restriction:With a large number of numbers, it can take some time for the macro to generate all possible combinations.

Benefits: Works on all versions of Excel 2010 - 365; provides two forms of output: strings of values ​​and numbers separated by commas.

exercise book

Finding combinations that equal a certain sum - examples(.xlsm file)