3 ways to find all combinations from a set of numbers that sum to a specific total.
Finding combinations of values that add up to a given sum is a common problem in data analysis. For example, you may want to know all possible combinations of items that can be purchased with a given budget, or all possible ways to allocate resources to meet certain requirements. In this article, we will 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 handling large datasets or complex constraints.
Find a combination of numbers equal to a given sum with Excel Solver
Regrettably, none of inbuilt Excel functions can help you identify the numbers that add up to a given total. Luckily, Excel provides a special add-in for solving linear programming problems. The Solver add-in is included with all versions of Excel but is not enabled by default. If you are not familiar with this tool, here's a great article on how to add and use Solver in Excel.
With the Solver add-in activated in your Excel, proceed with the following steps:
- Create the model.
For this, enter your set of numbers in one column (A3:A12 in our case) and add a blank column to the right of your numbers for the results (B3:B12). In a separate cell (B13), enter a SUMPRODUCT formula similar to this:
=SUMPRODUCT(A3:A12, B3:B12)
- Run the Solver add-in.
On the Data tab, in the Analysis group, click the Solver button.
- Define the problem for Solver.
In the Solver Parameters dialog box, configure the objective and variable cells:
- In the Set Objective box, enter the address of the formula cell (B13).
- In the To section, select Value Of and type the desired sum value (50 in this example).
- In the By Changing Variable Cells box, select the range to be populated with the results (B3:B12).
- Add the constraints.
To specify the constraints, i.e. the restrictions or conditions that must be met, click the Add button. In the Add Constraint dialog window, select the resulting range (B3:B12) and pick bin from the drop-down list. The Constraint will be automatically set to binary. When done, click OK.
- Solve the problem.
When taken back to the Solver Parameter dialog window, review your settings and click the Solve button.
A few seconds (or minutes) later, the Solver Results dialog box will appear. If successful, select the Keep Solver Solution option, and click OK to exit the dialog.
As a result, you will have 1 inserted next to the numbers that add up to the specified sum. Not a user-friendly solution, but it's the best that out of the box Excel can do.
For visualization purposes, I've highlighted the cells that give the desired sum in light green:
Limitation: Excel Solver can find, at most, just one combination of numbers that equals a specific sum.
Find all combinations that equal a given sum with custom function
To get all possible combinations from a given set of numbers that add up to a certain value, you can use the custom function below. If you are new to UDFs, you'll find a lot of useful information in this guide: How to create custom user defined functions in Excel.
How this function works
The main function, FindSumCombinations, calls a few subsidiary functions that implement smaller sub-tasks. The function named SumUpRecursiveCombinations executes the core algorithm that finds all possible sums in the specified range and filters those that reach the target. The ArrayToString function controls the form of output strings. Three more functions (SumArray, AddToArray, and CopyArray) are responsible for processing intermediate arrays: each time we create a temporary array, add one element from the source array to it, and check if the target sum is reached. The core algorithm is taken from this Stackoverflow thread, thank you guys for sharing!
Syntax
From the user's perspective, the syntax of our custom function is as simple as this:
Where:
- Range is the range of numbers to test.
- Sum is the target sum.
Note! The custom function only works in Dynamic Array Excel 365 and 2021.
How to use the FindSumCombinations function:
- Insert the above code into a Code module of your workbook and save it as a macro-enabled workbook (.xlsm). The step-by-step instructions are here.
- In any blank cell, enter a FindSumCombinations formula and press the Enter key. Make sure there are enough empty cells to the right to output all combinations, otherwise the formula will return a #SPILL error.
For example, to find all possible combinations of numbers in the range A6:A15 that equal the sum in A3, the formula is:
=FindSumCombinations(A6:A15, A3)
As with any other dynamic array function, you enter the formula is just one cell (C6 in the image below) and it populates the results into as many cells as necessary. By default, the comma-separated strings are output in a row:
To return the results in a column, wrap the custom function into TRANSPOSE like this:
=TRANSPOSE(FindSumCombinations(A6:A15, A3))
To output the strings in the form of an array enclosed in curly braces, modify the ArrayToString function as follows:
The results will look similar to this:
Limitation: This custom function works only in Dynamic Array Excel 365 and Excel 2021.
Get all combinations that equal a given sum with VBA macro
The custom function described above returns the combinations of numbers as strings. If you'd rather have each number in a separate cell, this macro will be helpful. The code is written by another Excel expert Alexander Trifuntov who has been actively helping users to solve various Excel problems on this blog.
Start by adding the following code to your workbook. For the detailed steps, please see How to insert VBA code in Excel.
Next, create a UserForm with the following design and properties:
After completing the form's design, add the code for the form. For this, right-click on the form in the Project Explorer and select View Code:
Tip. Instead of re-creating the form from scratch, you can download our sample workbook at the end of this post and copy the Code module and UserForm1 from there (see how to copy a macro to another workbook). To make the macro accessible in all your workbooks, copy it to the Personal Macro Workbook.
With the code and form in place, press Alt + F8 and run the FindAllCombinations macro:
On the form that pops up, define the following:
- The range with the source numbers (A4:A13)
- The target sum (50)
- The upper-left cell of the destination range (C4).
When specifying the upper-left cell of the output range, make sure there is at least one blank row above (for headers) and enough blank cells down and to the right. If there are insufficient blank cells, your existing data will be overwritten.
Clicking OK will produce the result like shown in the screenshot below:
- In C4:C6, you have the combinations of numbers as comma-separated values.
- Columns F, G and H hold the same combinations of numbers, with each number residing in a separate cell.
- In D4, you have the target sum.
This form of output makes it easier to check the result - just enter the SUM formula in cell F13, drag it to the right across two more cells and you will see that each combination of numbers adds up to the specified value (50).
Limitation: For a large set of numbers, the macro may take some time to generate all possible combinations.
Advantages: Works in all versions of Excel 2010 - 365; provides two forms of output - strings of comma separated values and numbers in separate cells.
In conclusion, finding all combinations of values that equal a certain value is a powerful tool for solving a wide range of data analysis problems. Hopefully, this article has provided you with a useful starting point for exploring this topic further and handling similar problems in your own work. Thank you for reading!
Practice workbook
Find combinations that equals a given sum - examples (.xlsm file)
117 comments
Just if each output string was written one below the other in a column, will be better and It would be much easier to use, especially when there are a lot of states obtained.
I see now. You explained it before. Thanks a lot again.
Great. Thank you.
Hi Alex,
This is so interesting. I've been trying to find a formula to get the combination of boxes that has X qty and evenly distribute them depending on how many group. Can you help on how to get the box id combination so I can get the maximum qty combination.
For example below:
18694 (the maximum combination that is less than 19213 using manual calculation)
LotID LOT QTY # OF BOX BOXID BOX QTY # OF GROUP QTY PER SECTION (LOT QTY / 2) BOX COMBINATION
A 38427 21 2 1726 2 19213 6,7,10,14,16,17,18,20,22,24
A 38427 21 3 1804
A 38427 21 4 1806
A 38427 21 5 1776
A 38427 21 6 1874
A 38427 21 7 1855
A 38427 21 8 1798
A 38427 21 9 1836
A 38427 21 10 1855
A 38427 21 11 1814
A 38427 21 12 1851
A 38427 21 13 1837
A 38427 21 14 1865
A 38427 21 15 1692
A 38427 21 16 1858
A 38427 21 17 1854
A 38427 21 18 1865
A 38427 21 20 1863
A 38427 21 21 1793
A 38427 21 22 1937
A 38427 21 24 1868
Hi! I think it would be better for your task if you would use the Excel Solver.
Hi,
I have a set of dealers with their SKU wise sale in qty, I want to find out the top 50% / 30%/ 10% Sku sales for each dealers
Can you help me in this please.
Regards
vinod
Hi! Assume you have the following columns in your dataset: Dealer, SKU, Sales Quantity.
Sort the Data: First, sort by Dealer.
Then, sort by Sales Quantity in descending order. Read more: How to sort in Excel by row, column names and in custom order.
Add a column D called Cumulative Sum.
Add another column E called Cumulative Percentage.
For example, let's assume your data starts from row 2 (with headers in row 1):
In cell D2 (with headers in row 1), use the SUMIF formula:
=SUMIF(A$2:A2, A2, C$2:C2)
In cell E2, use the formula:
=D2/SUMIF(A:A, A2, C:C)
Copy it down along the column.
Create new columns for Top 50%, Top 30%, and Top 10%.
In cell F2 (Top 50%), use the IF formula:
=IF(E2<=0.5, "Top 50%", "")
This will help you identify the top 50% SKU sales for each dealer.
hi,
thanks for a great solution...
why is arNumbers(indI) changing 103.99 to 104???
peter
Hi Peter!
The Long data type in VBA can only store whole numbers (integers), so indI can't take on a value like 103.99.
Hi,
This is a great solution and works well - I wondered if you could help me with a query though. instead of returning the number values is it possible to return the values one column to the left (which in my case are text descriptions?)
Thanks! :)
Hello Paul!
You can get the numbers, and then extract their corresponding text values from another column using INDEX MATCH formula.
Hi,
Thanks for the response - I've been using the custom function and my dataset has some duplicate numbers (exactly the same) and also they are monetary figures so have a 2 decimal places.. so not sure INDEX MATCH would provide what I need.
I've noticed that some of the results aren't accurate to 2 DP.. i think there must be some rounding or tolerance in how it provides the results? - not sure how to improve this - a neat solution I just wished it was a little more robust/accurate
Hello Paul!
To avoid rounding problems in Excel, I recommend using the integer numbers. If your numbers have two decimal places, you can multiply them by 100, do a calculation, and then divide the chosen numbers by 100.
find all combinations that equal a given sum in excel without round
How to find the largest possible whole value instead of a given value. I'm work with large decimal datasets and would like to solve for the largest possible whole number instead of playing around with given values.
Hello! You can find the maximum sum using given values using Solver tool. For the detailed instructions, please see: How to use Solver in Excel with examples.
Hi! unfortunately I was not able to figure it out with the linked article.
Please see below example for finding the largest possible integer sum of the dataset
0.007
0.0055
0.0027
0.0027
0.0025
0.0024
0.0029
0.0027
0.0077
0.0079
0.115
0.1426
0.2245
0.081
0.1348
0.162
0.7962
0.2116
1
1
0.532
0.6756
From changing the value I enter in the "target sum", I found the max to be 4, however, I was wondering if there is a way to have it solve directly for the combination that will sum to the largest possible integer of the dataset instead of playing around with target sum. This will be really helpful if I have a larger data set with higher values to solve for.
Hi! It is not possible for me to set up all the necessary rules for the Solver tool for you in your workbook. In this blog, we give advice to users, but we don't do their work for them.
Hi! I was wondering if there is a way to find combinations containing repetitions, as this formula given does not allow for repeats.
For example, I want to find possible combinations to achieve sum of 100, and my options are 10, 20, 30, 90. With this formula, the only combination identified is 10, 90.
Is there a way to show other combinations like 10, 30, 30, 30 or 20, 20, 30, 30, for example? Thank you!
Hello Penny!
Unfortunately these functions and macros only allow you to use each value once. If each value can be used multiple times, the number of possible combinations is huge.
The articlue is excelent !!!! Thanks for sharing !!
The solution works fine.
I'm interested in getting the list of combinations in a given range, not only de exact value.
Is there a way to achive this?
I read a couple of months ago that the solution wasn´t available but I wonder if it is now,
Regards,
Marcelo
Hi! To get all combinations in a given range, you need to modify the VBA code of the function presented above. We do not modify VBA code on request, but you can try to do it yourself.
Hi.
I tried the solver method and I noticed it does not work for over 200 cells. Please what can I do if I'm working with over 1000 cells?
Hi! This depends on your computer's technical capabilities and available RAM.
The requirement is to display all the combination from the list of decimal entries for the value of Total sum without repetition of numbers with condition only 6 entries to be consider for SUM.
For Example: SUM is 50, Table entries are 1- 30 numbers with decimals . Here the condition is for total SUM 50, only any 6 entries should choose without repetition of values from the initial table .
Output : Display all the combination for the SUM 50 in column
Hi! Try using the function provided in the article above. We do not do VBA code creation or customization on request.
Thank you for prompt response!
The above solution doesn't meet my requirement.
Wishing you great day ahead.
I actually want something different.
I want a formula to cut the values from the range whose sum equals to 228 and paste them in separate row.
then value being cut down it again pick values from the data range and paste them again in different row.
and this goes so on until no value remain in data range whose sum equal to 228.
Please reply
Hi! We do not create or customize VBA code if requested.
How to find all combinations of numbers that equal given sum in Excel?
This was my question and the solutions you have given is superb and it worked. I'm a regular Excel user hence this information too important to me.
Thank you so much.
Hi, this is great. thank you.
If my list does not contain data that add to my specific number (& therefore, solver is unsuccessful), is there a way that excel can output the "closest" value that can be reached using the data?
For example if I have list of data & I'm trying to find 4000.
But it is no possible and solver "unsuccessful".
But, for example, 3999 is possible and close to my value.
Thanks,
Hi! To find the "closest" value to 4000 in the target cell A1 that can be reached using the data, you can write the formula =ABS(A1-4000) in cell B1. You can then use the Excel Solver to find the minimum value in cell B1 by modifying cell A1.
I hope it’ll be helpful.
Hi, thanks for sharing your knowledge, i have to do the same thing but with 547 variables, where do i modify the VBA to do it?
I'm sure that Excel and your computer's abilities will not be enough to calculate and show such a number of combinations.
How to generate/ bring the values of all possible combination formates/ complete possibilities in excel.? Eg Question - from A To Z alphabets going to pick for letter 4 letter combinations like ABCD, ACDE, ADEF, etc., please understand I am not looking for this numerical answer 14,950. Kindly explain. Thanks.
Hi! We have a tool that can solve your task in a couple of clicks. The Random Generator for Excel fills the cells with random numbers, dates, and strings. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
200 mm
175 mm
1907 mm
201 mm
164 mm
HOW TO SUM IN EXCEL WITHOUT REMOVEING mm
Hi! You can extract numbers up to a space from the text and then sum them.
Using the TEXTBEFORE function:
=SUM(--TEXTBEFORE(A1:A5," "))
Using the SEARCH function:
=SUM(--LEFT(A1:A5,SEARCH(" ",A1:A5)))
Numbers in the form of text are converted into numbers using a mathematical operation. Read more: Excel: convert text to number with formula and other ways.
I have 6 parameters (let’s call them A,B,C,D,E,&F) and each parameter can have a possible score of 1 thru 95. I am trying to determine all combinations of the 6 parameters that can sum to 100. How do I do this in EXCEL without EXCEL program crashing?
VR,
JPC
Hello Sir
Need your help regarding this method: Find all combinations that equal a given sum with custom function.
In this tutorial, your values can equal the target number but , What If we would like to Find all closest combinations to a given sum without exceeding It (I mean less or equal)? (assuming that the total might not equal to the target value).
Also would be helpful if the second combination does not integrate any value listed in the first result , the 3rd combination alike , and so on.
Looking forward to your reply
B Rgds
Hi! To do this, try using Excel Solver. However, it will only allow you to find one combination. Read more: Excel Solver tutorial with step-by-step examples.
Hi! Such a huge number of variants, Excel will not be able to calculate. This is 869107785 combinations of numbers.
You won't even be able to write them down on the worksheet.
I have no background experience with anything like this. I would love to get this macro to work properly. I get to a point of running the findsumcombinations macro, but keep getting "need object" error 424. Then when I go and re type in the formula my entire excel freezes up. Any help would be appreciated.
Thank you
Hi! Regrettably, I was unable to replicate your issue, as I am unaware of the actions you take and the numbers you select. Please read carefully and follow the recommendations in the article above. Or describe the problem in detail.