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

3 ways to find all combinations of a set of numbers that add up to a given sum.

Finding combinations of values ​​that add up to a specific sum is a common problem in data analysis. For example, you might want to know all the possible combinations of items that can be purchased with a given budget, or all the ways you can allocate resources to meet specific needs. In this article, we'll explore how to use Excel Solver and VBA to accomplish the task. We will also discuss some potential pitfalls and limitations of each approach that may arise when dealing with large datasets or complex constraints.

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)Find all number combinations that equal a specific sum in Excel (1)

  2. Run the solver plugin.

    About itDataguide in whichTo analyzegroup, click onresolvingI like.Find all number combinations that equal a specific sum in Excel (2)

  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).
    Find all number combinations that equal a specific sum in Excel (3)
  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.Find all number combinations that equal a specific sum in Excel (4)

  5. To solve the problem.

    When he is brought backSolver ParameterPop-up window, review your settings and clickResolverI like.Find all number combinations that equal a specific sum in Excel (5)

    A few seconds (or minutes) afterSolver resultsdialog box appears. If successful, select thekeep solverand click OK to exit the dialog box.Find all number combinations that equal a specific sum in Excel (6)

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:Find all number combinations that equal a specific sum in Excel (7)

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.

explicit public option functionFindSumCombinations(rngNumbersSerange, lTargetSumSe Largo)DarkarNumbers()Se Largo, Paper()Se Largo DarkarRes()Se Chain DarkIndonesianSe Largo DarkceldaCurrSeAreaRedimarRes(0)ErngNumbers.Account > 1Then RedimarNumbers(rngNumbers.Count - 1) indI = 0For allceldaCurrEmrngNumbers arNumbers(indI) = CLng(cellCurr.Value) indI = indI + 1NextceldaCurrFinancial helpSumUpRecursiveCombinations(arNumbers, lTargetSum, part(), arRes())fin E Redim To preservearRes(0A Ubound(arRes) - 1) KombinationenFindSum = arResfin function Private SubSumUpRecursiveCombinations(Numbers()Se Largo, MetaSe Largo, Paper()Se Largo,PorRefarRes()Se Chain)DarkSSe Largo, EUSe Largo,JSe Largo, numberSe Largo, indResSe Largo Darkleft()Se Largo, parteRec()Se Largos = SumArray(Teil)Es = targetThenindRes =Ubound(arRes)Redim To preservearRes(0AindRes + 1) arRes(indRes) = ArrayToString(parte)fin E Es > targetThen salida Sub E(NO NOnumbers) <> 0Then Foreu = 0A Ubound(numbers) delete remainder() num = numbers(i)Forj = me + 1A Ubound(Numbers) AddToArray verbleibend, Numbers(j)Nextj Erase partRec() CopyArray partRec, parte AddToArray partRec, num SumUpRecursiveCombinations restantes, target, partRec, arResNextEUfin Efin SubPrivate functionArrayToString(x()Se Largo)Se Chain DarknorteSe Largo, resultSe ChainResult = x(n)Fornorte =LBound(x) + 1A Ubound(x) result = result &","& x(n)NextnArrayToString = resultfin functionPrivate functionSummeArray(x()Se Largo)Se Largo DarknorteSe LargoSomaArray = 0E(NO NOx) <> 0Then Fornorte =LBound(X)A Ubound(x) SummeArray = SummeArray + x(n)Nextnortefin Efin functionPrivate SubAddToArray(Matriz()Se Largo, XSe Largo)E(NO NOarr) <> 0Then Redim To preservearr(0A Ubound(topo) + 1)The rest Redim To preservearr(0A0)fin Earr(Ubound(oben)) = xfin SubPrivate SubCopyArray(target()Se Largo, source()Se Largo)DarknorteSe Largo E(NO NOfont) <> 0Then Fornorte = 0A Ubound(source) Target AddToArray, source(s)Nextnortefin Efin Sub

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!


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

FindSumCombinations(rango, suma)


  • 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:Find all number combinations that equal a specific sum in Excel (8)

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

=TRANSPOSE(FindSumCombinations(A6:A15, A3))Find all number combinations that equal a specific sum in Excel (9)

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

Private functionArrayToString(x()Se Largo)Se Chain DarknorteSe Largo, resultSe Chainresult ="{"& x(n)Fornorte =LBound(x) + 1A Ubound(x) result = result &","& x(n)Nextn result = result &"}"ArrayToString = resultfin function

The results will look something like this:Find all number combinations that equal a specific sum in Excel (10)

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.

PublicRefArray1Se ChainPublicSDSe variantPublictotal targetSe QuitePublicColObjectiveSe QuitePublicline of fateSe Quite SubCombination() UserForm1.Showfin SubfunctionGrayCode(ElementsSe variant)Se Chain DarkCodeVektor()Se Quite Darki,kk,rr,col1,line1,n1,mySe Quite DarklowerSe Quite, superiorSe Quite DarksublistSe Chain DarkNewSubSe Chain DarkdoneSe boolesch Darkawkward stepSe boolesch DarkSSDarkrayover()Se Chainkk = TargetCol rr = TargetRow col1 = TargetCol + 3 fila1 = TargetRow OddStep =TRUEinferior =LBound(article) above =Ubound(Elements) cells (rr - 1, kk) ="Result"Celldas(rr - 1, kk + 1) ="Additive"Cells(rr, kk + 1) = TargetSum Cells(rr - 1, kk).Fuente.Negrita =TRUECellulas (rr - 1, kk + 1).Font.Bold =TRUE RedimCodeVector (InferiorASuperior)'start all 0 Again Bisdone NewSub ="" Forme = inferiorASuperiorECodevektor (i) = 1Then ENovoSub="" ThenNovoSub=","& Artikel(i) SSS = SSS + Artikel(i)The restNovoSub = NovoSub &","& Artikel(i) SSS = SSS + Artikel(i)fin E fin E NextEUENovoSub="" ThenNovoSub="{}" 'empty setSubList = SubList & vbCrLf & NewSubESSS = meta de somaThenCells(rr, kk) = Mid(NewSub, 2) TargetArray() = Split(Mid(NewSub, 2),",") n1 =Ubound(Zielarray)Formi = 0An1 cells ( row1 , col1 ) = TargetArray ( and ) row1 = row1 + 1Nextand col1 = col1 + 1 queue1 = QueueObjetivo rr = rr + 1fin ESS = 0' now update the code vector Eawkward stepThen 'just rotate the first bitCodeVector(child) = 1 - CodeVector(child)The rest 'first find first 1me = inferiorAgain WhileCodeVector(i) <> 1 i = i + 1to wave 'done if i = top: Eme = aboveThenpronto =TRUE The rest 'if not done, invert the *following* bit:i = i + 1 Codevektor(i) = 1 - Codevektor(i)fin E fin Eodd step =NOawkward step'Toggle between even and odd steps to waveGrayCode = sublistfin function

Then create a UserForm with the following layout and properties:Find all number combinations that equal a specific sum in Excel (11)

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:Find all number combinations that equal a specific sum in Excel (12)

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.Find all number combinations that equal a specific sum in Excel (13)

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.
Find all number combinations that equal a specific sum in Excel (14)

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). .Find all number combinations that equal a specific sum in Excel (15)

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.

In summary, finding all combinations of values ​​that match a given value is a powerful tool for solving a variety of data analysis problems. I hope this article has provided a useful starting point for exploring this topic further and addressing similar questions in your own work. Thanks for reading!

exercise book

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

You might also be interested in

  • Excel SUM formula to sum a column, rows or just visible cells
  • 5 Ways to Sum a Column in Excel
  • How to Add and Count Colored Cells in Excel
  • How to Find Running Total (Running Total) in Excel
Top Articles
Latest Posts
Article information

Author: Dan Stracke

Last Updated: 02/14/2023

Views: 6510

Rating: 4.2 / 5 (63 voted)

Reviews: 86% of readers found this page helpful

Author information

Name: Dan Stracke

Birthday: 1992-08-25

Address: 2253 Brown Springs, East Alla, OH 38634-0309

Phone: +398735162064

Job: Investor Government Associate

Hobby: Shopping, LARPing, Scrapbooking, Surfing, Slacklining, Dance, Glassblowing

Introduction: My name is Dan Stracke, I am a homely, gleaming, glamorous, inquisitive, homely, gorgeous, light person who loves writing and wants to share my knowledge and understanding with you.