In this tutorial you will learn how to find out which numbers add up to a specific total in Excel. This we will do by two methods; one is by using the Solver add-in and other by using the VBA code.
Using Solver Add-In
This method is suitable if the numbers list is small. Basically, less than or equal to 10. But, if the list has many numbers (more than 10), this method is not recommended because it will take lot of time as the list size increase.
Follow these steps to find out the numbers that add up to a total.
Example: Suppose you have to receive the payment of 10 invoice from your customer. The customer issued a cheque to you by adding the amount from few of the invoices. You don’t know which amounts are added to calculate the total amount (355727) of cheque.
Step 1. List out the numbers.
Step 2. In a cell, use SUMPRODUCT function by taking the number range and another range next to it.
Step 3. Click on Data tab then Solver.
Note: IF Solver is not available, enable it from File Tab/Options/Add-Ins/Go/Solver/Ok.
Step 4. In the Solver window, link calculated cell (using SUMPRODUCT) to “Set Objective”. Link second range of SUMPRODUCT function ($D$5:$D$14) to “By Changing Variable Cells”. Put the total value (355727) in “Value Of” field.
Step 5. Add a subject to constraint by clicking on add button. It will open add constraint dialog box. Click on the Cell Reference and select D5:D14 range. From the drop-down list, choose “bin”. Then click on Add Button. The constraint will be added to Solver Parameters window.
Step 6. Click on the Solve button.
It will take a while to find out the values that are added to the specific total. At the bottom of Excel window, it will display the process. Once completed, it will populate binary digits (0 and 1) in D5:D14 range. The values corresponding to 1 are the numbers these are added to the total (355727).
Step 7. Click on “Ok” to close result dialog box.
Using the VBA Code
VBA Code method is works fine whatever the size of the numbers list and it is gives the result quickly. In this method, we have to make a custom function to find out the solution by installing the code in Excel VBA.
Follow these steps to do this.
Step 1. Download the Code Notepad File. Click on below button to Download.
Step 2. Open the file, select all and copy the code.
Step 3. In the Excel workbook, click on “Developer” tab, then “Visual Basic”.
Step 4. Click on “Insert” tab, then “Module”.
Step 5. Paste the copied code.
Step 6. Close the VBA editor window.
Step 7. Use the function. It will display result by separating each added number by comma. See below image illustration for how to use the function.
Note: To use save this function and reuse after you open Excel file next time, save the Excel file as “Macro Enabled”. For this, Click on File Tab/Save As. In file type choose the “Excel Macro Enabled Workbook”. Then Save.
