Accounting 101 Excel Tips

Identifying Excel Entries that Add Up to a Specific Value

How familiar is this situation: you have a large number of entries in a spreadsheet and you need to determine which ones add up to a particular amount?  Personally, this occurs often as 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, but then learned a simple trick that utilizes Excel’s built in capabilities.  A task that would take as long as I could stand using trial and error now takes minutes thanks to the Solver Add-in.

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 do this, please visit this site.  Luckily, once you load this add-in you should never have to do it again.

Solver Pic 1

To begin, list all of the values of the individual entries in a column of the worksheet – 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 in column B and 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 and a cell that will be the difference between the target number and the sum of the products.  See below for clarification.

Solver Pic 3

Finally, it is time to determine which data entries 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 do 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 – 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):






Hopefully knowing how to do this will save you some time and replace the manual process of trial and error.

Peter holds a Master's Degree in Information Systems, has passed the first level of the CFA exam, and is currently working as a data analyst for a financial institution.

14 comments on “Identifying Excel Entries that Add Up to a Specific Value

  1. this was very helpful

  2. isidro

    Thank you, thank you, thank you. this saves me a lot of time in manual calculations. thank you.

  3. This is like the answer to a prayer – thank you!!!

  4. Also work at a CPA firm – this is perhaps one of the best excel functions I just learned.

  5. Frederick C. Widrick

    This processing is amazing! Is there a VBA code implementation?

  6. Thanks for this! How do I find all the possible solutions assuming there is more than one?

  7. 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.

  8. Great walk through of a feature I have wanted to add to my skill set for some time now. Thanks!

  9. Greetings From Rwanda. It really worked and saved me the time I used to take when reconciling. Thank you very much .

  10. 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!!

  11. Written in 2017, helped most in 2019 and still in 2020… 2 Years before the Thank you’s started rolling in. Thank you again.

  12. Much thanks! Trial and error was driving me to drink!

  13. 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.

  14. This is awesome! Thank you so much!

Leave a Reply

%d bloggers like this: