# Excel: Find a subset of numbers that add to a given total?

I have a column of numbers (lets say it's A1:A100) and I need to find a subset of them that sum to a certain total.

It's possible with the Solver add-in*. The following steps worked for me in Excel 2007 and 2010.

1. Designate a cell to hold the result (C1 for this example) - this is the target cell, and a column that excel can use for scratch-work (B1:B100 for this example)
2. In the target cell, enter the formula "=SUMPRODUCT(A1:A100,B1:B100)" (no quotes). This will calculate the sum of A1*B1+A2*B2+...etc
3. Select Open the solver (Data tab, Analysis group)
4. The target cell should be obvious (\$C\$1 for this example)
5. For 'Equal To:' select 'Value of:' and enter the desired value
6. In the 'By Changing Cells' enter "\$B\$1:\$B\$100" (no quotes, and it may be necessary to initialize these values to 0 yourself)
7. Add a constraint to the cells that can be changed. In the pull-down, select 'bin' (Binary). This restricts the values of these cells to 0 (removing the corresponding A cell from the sum) or 1 (adding the corresponding A cell to the sum).
8. Click 'Solve' and wait. The numbers that are part of the subset you're looking for will have a 1 in the B column

If the solver is taking a long time, you can help it out by removing rows that obviously won't work (total is in dollars, and only one row has nonzero cents)

Bonus: You can have excel automatically highlight the cells that you're looking for by adding conditional formatting to those cells. Select all of the cells you want to format and from (Home tab)>>(Styles group)>>Conditional formatting>>New Rule select 'Use a formula to determine which cells to format'. In the formula, enter '=\$B1=1' (no quotes) which will evaluate to true if the corresponding row in the B column is 1. For the format, you can add whatever you want (bold, italic, green fill, etc).

Another easy way to find the important rows is to sort column B Z->A, and all the 1's will come to the top.

*The solver add-in can be installed with these steps

1. Click the Microsoft Office Button, and then click Excel Options.
2. Click Add-Ins, and then in the Manage box, select Excel Add-ins.
3. Click Go.
4. In the Add-Ins available box, select the Solver Add-in check box, and then click OK. (If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in.)
5. If you get prompted that the Solver Add-in is not currently installed on your computer, click Yes to install it.
• In Excel 2013 you need to uncheck "Ignore Integer Constraints" from the options menu. Otherwise you get non INT values for 0 and 1 – Omar Shahine Jul 30 '13 at 14:44
• In order to make this work properly: 1. The formula in Cell C1 should be sumPRODUCT (not just sum) 2. The cells in Column B should all have zero input in them. – user268218 Oct 30 '13 at 18:18
• Thanks! For me, the old steps still work, but I like sumproduct better because it doesn't rely on the array formulas feature. – Nate Parsons Oct 30 '13 at 23:52
• anyway to find all the combinations... although there is one solution found by solver.. how to find the other solutions??? my array is here: v = [1100, 1105, 11830, 14790, 2325, 2455, 2555, 2935, 3050, 3150, 3185, 3370, 3475, 350, 3530, 3590, 3680, 3745, 885, 9624] sum = 43029... solution1 = [350, 1100, 2325, 2455, 2555, 2935, 3150, 3745, 9624, 14790], solution2 = [350, 885, 1100, 1105, 2325, 2455, 2555, 3530, 3590, 3680, 9624, 11830] – ihightower Jul 8 '15 at 8:09

There is a low cost Excel Add-in SumMatch, which will highlight the subset of numbers that add up to a target sum.

• Hello AIsoft, good find. Maybe next time you can add some more information. – nixda Dec 30 '12 at 3:24
• @pnuts Did I made mistake? I don't understand what you mean with AUD\$30 – nixda Dec 30 '12 at 3:41
• @pnuts Ah ok, that's a good point. – nixda Dec 30 '12 at 3:48
• Note for myself: AUD\$30 = 30 australian US Dollar. Its not a reference to an Excel cell [AUD30] with a fixed column.... :D – nixda Dec 30 '12 at 5:32
• The price tag is a drawback, as is the lack of clear instructions. Anything more than 1 sentence + 1 picture from the product's website, really. – Nate Parsons Jan 9 '13 at 21:53