If you use excel, then we have all been in the same position. Imagine you have a large number of entries in a spreadsheet and you need to determine which ones add up to a particular amount? How do you do this? Well, you are in luck. We have a simple trick that will help in identifying excel entries that add up to a specific value.
Personally, this situation arises often. I frequently analyze spreadsheets full of data in the form of account transactions. I used to spend way too much time manually adding numbers together trying to find my predetermined total. After years of doing things the manual way, I learned a simple trick that utilizes Excel’s built in capabilities. A task that would take so long using trial and error now takes minutes thanks to the Solver Add-in.
For more helpful excel hints, you might want to check out this pamphlet. It lists out all of the important tips and tricks you need to get the most out of Excel.
How to identify excel entries that add up to a specific value:
If you are looking to figure out how to use excel to help in identifying excel entries that add up to a specific number, you need to do one thing first. The first step in this process is to make sure Excel’s Solver Add-in is enabled.
To determine this, go to the Data Tab of your spreadsheet and look for the Analysis section. You should see ‘Solver’ all the way to the right. If you do not see it, please enable it. If you are unsure of how to enable solver, please visit this site. Luckily, once you load this add-in you should never have to do it again.
Data Format is Important
When identifying excel entries that add up to a certain number, make sure you data is in a particular format. You will need to list all of the values of the individual entries in a column of the worksheet. To make things easy, name this column Data.
In this example I will be using column B. In the adjacent column, column C, enter the number ‘1’ next to all of your values. Name this column Multiplier.
Lastly, in a third column, enter a formula that multiplies the first two columns. In this example, you would enter =B2*C2. The spread sheet should look something like this (click photo to enlarge):
Once you have entered your data and added the two additional columns, the next step is to create an input cell for the value you are seeking. You will also need to include a cell which will sum all of products. Lastly, add a cell that will be the difference between the target number and the sum of the products. See below for clarification.
Finally, it is time actually start identifying excel entries that add up to your target number. The way this works is that the Solver either keeps the multiplier 1 or changes it to a 0. This then changes the product which affects the Sum of Products and Difference totals calculated in the previous step. The solver keeps testing combinations until the difference between the Target and Sum of Products is 0.
So, configure the solver to meet the following constraints:
- Set Objective: enter the cell that has the difference amount in it (in this example it is G4) and remember to add the absolute reference symbols.
- To: click on the ‘Value of:’ button and enter in ‘0’
- By Changing Variable Cells: the variable cells are the Multiplier column of the worksheet. Enter these cells and again, remember to add the absolute reference symbols. In this example, it would be cells C2:C31.
- Subject to the Constraints: one constraint needs to be added that will make sure the multiple that is changed in the multiple column is either 1 or 0. Click on the add button, select the values in cell C, and change the first drop down menu to bin. Click ok.
- Select a Solving Method: change the solving method to Simple LP in the drop down.
The solver should look like this (click photo to enlarge):
Now it’s time to hit Solve and start identifying excel entries that add up to your target. The solver will find the combination of numbers that add up to the target value specified in cell F4. However, there are two limitations to the solver: the solver limits the user to 200 variables as well as 100 constraints. After running this, the multiplier column is updated (click photo to enlarge):
The way the solver works is that it adjust the value in the multiplier column. It tests all of the different combinations until the difference between your target and the sum product is 0.
More on Solver
The solver tool is very flexible and can be used for a variety of tasks. These tasks can be as simple as identifying excel entries that add up to a target value. The more complex ones include data models, Monte Carlo simulation, and more. If you are used VLOOKUP and HLOOKUP formulas, the solver will be able to take things to the next level.
Hopefully knowing how to do this will save you some time and replace the manual process of trial and error. Again, if you found this article helpful, check out this pamphlet containing everything you need to know about Excel.
Also, for more excel tips and tricks check out our professional development section.
this was very helpful
Thank you, thank you, thank you. this saves me a lot of time in manual calculations. thank you.
This is like the answer to a prayer – thank you!!!
Also work at a CPA firm – this is perhaps one of the best excel functions I just learned.
This processing is amazing! Is there a VBA code implementation?
Thanks for this! How do I find all the possible solutions assuming there is more than one?
I find this funny that I found this not on an excel website but on a CPA website! I guess this is a common auditor problem.
Great walk through of a feature I have wanted to add to my skill set for some time now. Thanks!
Greetings From Rwanda. It really worked and saved me the time I used to take when reconciling. Thank you very much .
I have been needing something like this for so long to help me reconcile the company credit cards. Thank you so much for sharing and for making the instructions easy to follow!!
Written in 2017, helped most in 2019 and still in 2020… 2 Years before the Thank you’s started rolling in. Thank you again.
Much thanks! Trial and error was driving me to drink!
Peter, thanks for this. It will save me time and lessen my Advil dosage.
A corollary to your framework: if the user knows the exact number of transaction involved in the mystery sum, the user can add another constraint. In your example, sum the binary column in cell C32 (i.e. enter =SUM($C$2:$C$31), then add to Solver a constraint of $C$32 = X, where X is the target number of transactions. You can also use >= and <=.
Also, if you have a huge list of transactions, you likely get "false positives," meaning the transactions add up to the target, but they are not the appropriate transactions, especially true if you have many round amounts (XXX.00). Solver acknowledges that with the disclosure in the "Solver Results" — "It is possible that better integer solutions exist." An alternate combination of numbers isn't necessarily" better"; it is just more applicable to your requirement. So need to have a critical eye to determine if you are getting what you want.
Do you know if Solver can spit out multiple solutions? I don't think so, but thought I's ask.
This is awesome! Thank you so much!
Thank YOU! Saved me a lot of hassle trying to work out customer payments when they hadn’t sent a remittance to go with a bulk payment.
This is simply amazing … thanks a bunch
Absolutely incredible! Thank you!
IT guy here. Thanks for this. We’re trying to troubleshoot why certain invoices behave in a certain way and the only way that we can identify them is by deconstructing the sum. This has saved us a lot of time, and the our data series are simple enough that false positives haven’t been an issue.
can this work to solve to get as close to the objective number as possible, but not necessarily the exact number?
sorry – to clarify, as close to the objective number as possible, but not GREATER than that number.
Pingback: Code Bug Fix: there is a package or function in R that allows “Identifying Excel Entries that Add Up to a Specific Value” - TECHPRPR
Elementary person here, so excuse the question but really need help! I want to use the SOLVER but I don’t want to find the difference, I want to find a specific number. So, a random number I need to know what in my selection makes that number (but it’s not the difference. Does this make sense? Ex: I have 10, 50, 25, 100, 200. I want to know which of these numbers total 85. (I know we know the answer from this simple one but it’s often 30 or so numbers and much larger total I’m trying to find. Can this work for that?
This should still work. Calculating the difference is just something that needs to be done so solver can determine when the sum of the records has a difference equal to 0 compared to the target field.
OMG I tried and it worked well!!!! This add-in is incredible! Time-saver and life-changer! Of course thank you so much for the comprehensive instructions!
How could I make this work for multiple target numbers so that it doesn’t use numbers more than once?
I have been looking for something like this for months! I thought I was in lala land for even thinking such a thing exists. And then today when I got desperate enough I figured I’ll try my luck at googling my fantasy solution – only to discover it’s right under my nose, built into excel! Turns out I couldn’t use it though because there’s a max of 200 variables and I was working with more entries than that. Anything to do about that?
I’m so excited to use this in the future though
I have a list of 570 value. IS there anything out there can find a combination of the sample size?
Yup!!! I am glad i found this. Thanks for this great piece Sir.
Thank you, I had been looking for a way to do this long ago. You’re a life saver 😀
Amazing, saved hours upon hours of work
Thanks this was really helpful and to the point. Saved my day.
THANK YOU THANK YOU THANK YOU!
You’ve just saved me hours of my life!
This is AMAZING! Thank you 🙂
Seems to be working, but its been running for 16 hours straight without a conclusion (47 lines of data and circa. 5million combinations to date), is this normal?
This worked for me the first time I tried it. Now it doesn’t. I get either a “target cell must contain a formula” error, or if I fill in the target cell with “=[target value], then I get “a solution cannot be found” error. I tested it with a solution I know exists, and I still get that error. I am using Excel for Office 365. I know this article was written a while back; do you know if recent versions of Excel preclude this fix working? I sure need it! BTW, I am NOT using a table, and NOT allowing for any SPILL errors.
This is happening to me too!! It worked the first time and every time since that I have tried to use the solver for the same function on any other sheet, it cannot find a feasible solution. I can clearly see there is a solution but solver is not finding it. I hope someone can help us with this. I was so excited the first time it worked and was even going to share it with my Accounting team, but now I can’t get it to solve anything!!
This looks really helpful, however, it would have been good to include the variable limit at the very start! I have 6000 variables, worked through each step, only to find at the very end that this wouldn’t work for me.
The Excel is saying I have too many variables cells but its only 205 lines? Any suggestions?
Thanks for this solution! I followed the instructions but apparently the data set didn’t include any combination of entries that added to the target – and solver seems to have overridden the binary constraint and changed one multiplier to a fractional amount in order to force a solution! Any advice?
Is there a way to limit the number of entries to 5 and no repeats?
So out of 50 #’s you only pull 5 that could reach a total sum and there be no repeats of those 5?
You can add another parameter to solver where the sum of the multiplier column has to equal 5.
I am trying to set a maximum difference between the total and the target (rather than a specific number) and solver really doesn’t like it. Does anyone know a way? When I set the exact difference based on one of the values subtracted from the target it works fine, but when I try and set a max difference of 10 (which would give many options based on the dataset), it changes all the multipliers to 1. Does anyone have any advice?