Identify numbers in sum using Excel solver
Here is a useful feature I recently found googling for Excel solver examples.
I have summed some random values from the list A1:A11 in cell C1.
How do I find those summed numbers in C1?
I am going to use Excel Solver. Using more than 20 values (Column A) is not recommended unless you have a lot of spare time.
Watch a video
Install Solver add-in
- Press with left mouse button on Office button
- Press with left mouse button on Excel Options
- Press with left mouse button on add-ins
- Install Solver
- Press with left mouse button on OK
Interested in what other things Excel Solver can do? See category for Solver.
Setup formula
- Double press with left mouse button on B12
- Type =SUMPRODUCT(B1:B11,A1:A11) + ENTER
Recommended reading:
Recommended articles
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
Start Excel 2007 Solver
- Press with left mouse button on "Data" in the Ribbon
- Press with left mouse button on "Solver"
- Press with left mouse button on "Set Target Cell:" button
- Press with left mouse button on B12
- Type in "Value of:" window the summed value 2615,4
- Press with left mouse button on "By Changing Cells:" button and select B1:B11.
- Press with left mouse button on "Add" button
- Press with left mouse button on "Cell Reference:" button and select B1:B11
- Select "bin" in drop down menu (triangle)
- Press with left mouse button on "OK"
- Press with left mouse button on "Options"
- Enable "Assume Linear Model"
If you have a newer Excel version and can't find "Assume Linear Model", select Simplex LP in "Select s Solving Method" drop down list.
Then press with left mouse button on "Solve"button.
You are ready, ignore steps below. - I changed "Max Time:" to 1000 seconds
- Press with left mouse button on OK
- Press with left mouse button on "Solve"A couple of minutes later.
- Press with left mouse button on OK!
Recommended articles
Recommended articles
This article describes how to find a sum from a range of numbers using a user defined function. Let´s see […]
Recommended articles
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
Recommended articles
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
Combinations category
Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]
Find numbers in sum category
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]
Question:I would like to reduce the number of open items by identifying positive and negative amounts that net to zero […]
Solver category
As you probably already are aware of I have shown you earlier a vba macro I made that finds the […]
I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
The COMBIN function returns the number of combinations for a specific number of elements out of a larger number of […]
Excel categories
48 Responses to “Identify numbers in sum using Excel solver”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I found this solution of idendtify number in sum using solver in excel. its great. but i couldnt find excel solver in my office 2010.
i have did the same to install solver add in in excel option and then press with left mouse button on the solver add in and then okay.
but later i went to Data ribbon and couldnt find Solver option.
Could you please tell me what is the problem in my office 2010
It's in DATA section
I found this:
https://www.addictivetips.com/windows-tips/microsoft-office-excel-2010-solver-add-in/
Solver is located under the Data tab as shown in the screenshot below.
There seems to be something wrong with your excel version. I am using excel 2007 and I have no clue.
Sir;
many many thanks for your unmatched articles provided to the public, you saved my previous time at all
Once again i am thankful to you and my best wishes to you always
Thank you! My data set is 362 cells. It errors me with 'too many variable cells!'
Melynda,
Yes, it would take (almost) forever to calculate 362 cells.
You cannot use more than 100 variables when trying to find the sum of variables using solver. Also cannot have more than 200 constraints.
Thank you so much for sharing this!
It helped me a great deal instead of having to find all the possible combinations to get the value i require.
Thanks!
Sherlyn,
Thank you for commenting!
Fantastic solution, thanks!
is there any other formula to this operation?
Rey,
Only a custom function:
Excel udf: Find numbers in sum
There is now, see this post:
https://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/
Thank you so much! This is exactly what I was looking for. What a time saver! Would love to have this run with a press of a button with some VBA, looks like my next see if its feasible project.
Very Handy.
Thanks a lot! It is indeed a time saver...
I am happy you like it!
To be completely honest, I didn’t like it! I simply LOVE IT!
I always knew that Excel should be able to do this, but never realized how I could do it on Excel, so thanks a lot for sharing it!
I’m still trying to figure it out a way to have this method on a template, where I won’t need to call and setup the solver, whenever I need to use it.
Anyway thank you very much for the time you took to share with the community.
Cheers from Portugal
Ricardo,
Thank you for your kind words!
I’m still trying to figure it out a way to have this method on a template, where I won’t need to call and setup the solver, whenever I need to use it.
Read this post:
Excel udf: Find numbers in sum
Hello,
I have a range with around 100 cells. Is there anyway to do a range that large?
David,
Not that I know of.
I have data in 43 cells and still it is taking forever..is there any wayarouond any macro to help me with this
Vivek Singh,
No, not that I know of.
You can use Excel add-in called SumMatch. It will show all possible combinations, not just one.
Hi, Thanx for the informative article. However, the one major problem in using this solver function is that it takes a lot of time for finding the optimum solution if the quantum of data is huge.
In my field of work there are upto 10,000 rows and 5,000 colomns.. Hence finding a particular amount from such a huge database become difficult for excel.
Abhijit,
Yes, you are right.
Too Many adjustable cell... error said.
what to do next sir?
[…] Hope this helps: Identify numbers in sum using solver in excel | Get Digital Help - Microsoft Excel resource […]
Thank-you for this fantastic solution. You just saved me a ton of time trying to figure out variables that don't work for a solution. I tested this with numbers I knew would work and it's perfect! Finally, a use for Solver in my work.
Hi oscar
Im using excel 2010, my main problem is that whenever i set the solver and it validates a range of around 20 cells the number i get after i hit solve is not the exact target number i put. Concerning the binaries that give us the ability to identify the combination of numbers is giving me 0.27 for some numbers. I can't identify the problem, can it be that our client just paid wrong invoices? Or maybe a mistake have happened?
Than you in advance
Hi,
This is a neat solution to finding values used in a SUM function, but it does not take into account if there are 2 or more possible combinations of values which can be used to achieve the SUM amount. I'm very interested to find out if there is a method of identifying all possible combinations of values. Also, what happens using this method if there are duplicate values in the list of numbers.
Ron,
read this post: https://www.get-digital-help.com/2011/02/25/excel-udf-find-positive-and-negative-amounts-that-net-to-zero/
thank you very much!
I need a method, So when the sum of the values is not equal to the value in "Value of", then it gives me the sum of values less than the value in "value of"
Thank You..
I need to help to try to solve a problem using Excel Solver.
I work for a manufacturing company and we make cores in heights totalling from 10 to 300mm.
We can make individual cores in the following heights
10
15
20
25
30
35
40
50mm
If we receive an order for a core of over 50mm in height, say 75mm height, then we can "stack" two or more cores with say a 50 & 25mm or 40 and 35mm.
I would like to use solver to identify possible combinations using the fewest number of cores to produce the stack.
The only other constraint is that as well as wishing to use as few cores as possible, I would then also like to minimize the different combinations. So to achieve a core of 105mm in height I could use 2 x 40mm cores plus 1 x 25mm but I would prefer using 3 x 35mm as it means I am using all the same size.
Similarly for larger cores it would be better to use two different types and three etc.
Hope this makes sense.
Thanks
Ian
Ian,
Interesting question, unfortunately excel hangs after a while. I have tried multiple excel versions and computers. I don't know why.
I tried this:
Formula in cell D2: =C2*B2
Copy cell D2 and paste to cell range D3:D9
Formula in cell E2: =(C2>0)*1
Copy cell E2 and paste to cell range E3:E9
Formula in cell D10: =SUM(D2:D9)
Formula in cell E10: =SUM(E2:E9)
These are my settings in Solver:
Oscar
Thanks so much for taking the time to look at this. I replicated all your entries but I got the following warning message:
"Lower and upper bounds on variables allow no feasible solutionSome upper bound on a Variable Cell is smaller than a lower bound on that same Variable Cell."
Any ideas?
NB Wasn't sure why "Value Of: 70" although like you I then selected "Min"
Ian,
I replicated all your entries but I got the following warning message:
"Lower and upper bounds on variables allow no feasible solutionSome upper bound on a Variable Cell is smaller than a lower bound on that same Variable Cell."
I think you got the less than and greater than signs wrong? Check these constraint lines again.
$C$2:$C$9 <= 10 $C$2:$C$9 >= 0
NB Wasn't sure why "Value Of: 70" although like you I then selected "Min"
Sorry, that was an old value I forgot to delete.
I tried with a solution I know works but it is not the optimal solution and let the solver work from there. It then finds another solution but unfortunately not the best solution.
If I try again, it tells me:
Solver has performed 5 iterations for which the objective did not move significantly. Try a smaller convergence setting, or a different starting point.
Unfortunately it won't find the best solution.
The solver displays a message on the statusbar for each iteration, this slows down the calculations considerably and I believe it was done on purpose. They want you to buy their products.
Thanks again Oscar.
I re-typed my formula's to make sure I input them correctly.
I input 250 in d12 and hit solver expecting solver to tell me 5x50 i.e. 5 in C9. Is that correct?
But solver runs and after 28K+ subproblems, simply tells me
"Solver cannot improve the current solution. All constraints are satisfied."
Ian,
I input 250 in d12 and hit solver expecting solver to tell me 5x50 i.e. 5 in C9. Is that correct?
Yes, this is what I get:
Not the optimal solution.
I am using solver method: Evolutionary
However, if I change to solver method: GRG NonLinear
and start with zeros in cell range C2:C9
I get the optimal solution, cell C9 = 5
If I input 105 in d12 I also get the optimal solution, c7 = 3, solver method: GRG NonLinear
I also tried 115 and 160 in d12 and they all return the optimal solution, if I use GRG NonLinear.
I tried 125 and got this:
and this telling me to change integer tolerance to 0% in the options.
https://www.solver.com/excel-solver-integer-constraints-and-integer-optimality-tolerance
I changed that option.
but solver returned the same solution, not the optimal solution.
The optimal solution is 2x50 + 25 or 3x35 + 20, there are perhaps more.
Great solution, very useful.
Hi! Thank you so much for sharing thia. It helps me a lot. I want to ask, do excel solver can generate more than one combination of numbers for certain value?
This article shows you how to find multiple combinations, however it uses array formulas:
https://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/
I tweaked his formula to find numbers closest to sum:
https://www.get-digital-help.com/2015/02/19/find-numbers-closest-to-sum/
This user defined function is a lot quicker than solver, it also outputs all combinations:
https://www.get-digital-help.com/2011/02/25/excel-udf-find-positive-and-negative-amounts-that-net-to-zero/
clever solution. Thanks, it helped me a lot in my work
Hi. I tried this by using a target of the number 10. In my column, I used the numbers:
5
5
8
2
7
3
1
2
6
4
I was trying to see the various possibilities that can equal 10. Just by looking at it you can see there are several. However, the tactic in the instructions returned zeros next to all numbers except 6 and 4, next to which were the number one. Is this instruction not meant for this application?
I need this for something more large scale, but I used smaller number for easy head math. If this is not the right procedure for what I am trying to accomplish, does anyone know what is?
HelpMe
I recommend you read this post if you want to find all possible solutions using an array formula:
https://excelxor.com/2015/02/10/which-numbers-add-up-to-total-2-multiple-solutions/
or this post if you want to use a UDF:
https://www.get-digital-help.com/2011/02/22/excel-udf-find-numbers-in-sum/
Hi Oscar,
I am trying to follow your steps, but I get stuck at step 11. That dialog box doesn't pop up. What pops up is the same options box from your December 21, 2016 at 9:03 am post. (sorry couldn't post an image) I don't see anywhere to tick "assume linear model" Could you please provide instructions from step 11 with this other dialog box?
Gman,
You are right, it (assume linear model) seems to be missing in later excel versions.
greetings Guru Oscar is it possible for me to ask you to email me the sample of your Identify numbers in sum using Excel solver ( https://www.get-digital-help.com/identify-numbers-in-sum-using-solver-in-excel/ ) if its still available. very impressive what you have done. thank you