Excel Solver: Find a Target Sum with Changing Cells

To make a set of numbers add up to a target in Excel, create a total formula, use Solver to set that total to the target, and let Solver change selection cells that indicate which numbers to include. For example, Solver can choose a subset of listed amounts that totals 100. First enable the Solver Add-in if it is not already available, then define the objective, changing cells, and any rules the answer must follow.

Solver is useful when there are many possible combinations or when the values you can change are subject to restrictions. It does not guess a combination from a plain list on its own: your worksheet needs a formula that calculates the total and decision cells that Solver is allowed to adjust.

What You’ll Need

Excel for Microsoft 365 on Windows, with the Solver Add-in enabled.
A list of numbers that may be included in the total.
A target sum, such as 100.
A worksheet formula for the calculated sum, plus cells Solver can change to represent your choices.

The example below treats each listed number as an item that can be used once or not used. If you instead want Solver to change the amounts themselves, see the Tips section for how to adapt the model.

Step-by-Step

Enter the numbers and create decision cells. In a blank worksheet, enter the candidate amounts 12, 18, 25, 30, and 40 in A2:A6. Enter 0 in each of B2:B6. Column B will represent whether the corresponding amount is selected: 0 means do not include it and 1 means include it. Add headings such as Amount and Include in A1 and B1 if they help you keep the layout clear. Leave the decision cells as ordinary numeric cells; do not type formulas into them.
Calculate the selected total. With candidate amounts in A2:A6, selection indicators in B2:B6, and the result intended for D2, put this formula in D2: =SUMPRODUCT(A2:A6,B2:B6). SUMPRODUCT multiplies each amount by its matching indicator and adds the products. At the initial setting, all indicators are zero, so D2 displays 0. When B2 is 1, for example, the 12 in A2 contributes 12 to the total.
Confirm that Solver is available. On Windows, select File > Options > Add-ins. At the bottom, next to Manage, choose Excel Add-ins and select Go. Check Solver Add-in and choose OK. If Excel asks to install or enable it, accept the prompt. Then look for Solver on the Data tab, usually in the Analysis group. If it was already enabled, go straight to Data > Solver.
Open Solver and set the objective. Choose Data > Solver. In the Solver Parameters window, set Set Objective to D2, the cell containing the calculated sum. Under To, select Value Of and enter 100. This tells Solver to look for decision-cell values that make the formula result equal 100. Do not set the objective to one of the amount cells; the objective must be the formula cell that calculates the total.
Choose the cells Solver can change. In By Changing Variable Cells, enter B2:B6. These are the inclusion indicators. Solver will adjust these cells while keeping the candidate values in A2:A6 unchanged. Selecting the wrong range is a common cause of a result that does not reflect the intended problem, so confirm that each changing cell lines up with one candidate amount.
Require each indicator to be binary. Choose Add in the Solver Parameters window. For the cell reference, enter B2:B6. In the relation list, choose bin, then confirm the constraint. A binary constraint restricts each cell to 0 or 1, so Solver cannot select a fraction of an item or use an item more than once. Check that the completed constraint appears in the Solver window before continuing.
Select the solving method. In Select a Solving Method, choose Simplex LP. The total formula is linear, and binary decisions are allowed in this model, so Simplex LP is the suitable method. Keep the Solver options at their defaults for this small example. If you build a model with nonlinear formulas later, you may need a different method; do not change methods just to work around a missing or incorrectly configured constraint.
Run Solver and review the result. Select Solve. For this example, Solver can choose 12, 18, 30, and 40, which add up to 100; the corresponding indicators will be 1, 1, 0, 1, and 1. The arithmetic is 12 + 18 + 30 + 40 = 100. Solver may display another valid combination if more than one exists. When the Solver Results message appears, choose Keep Solver Solution to retain the values it found, then choose OK.
Verify the worksheet before relying on it. Check that D2 displays 100, that every cell in B2:B6 is either 0 or 1, and that the selected amounts really add to the target. If you want to keep a record of the chosen combination, add a note or copy the results to another area before making a new Solver run. Solver changes decision cells, and a later run can replace the earlier solution.

Common Mistakes

Using the wrong objective cell: Set Objective must point to the calculated total, not to an input amount or an empty cell. If Solver has no formula cell to evaluate, it cannot calculate whether a proposed selection reaches the target.

Forgetting the binary rule: Without the bin constraint, Solver may set an indicator to a decimal such as 0.4. The resulting total could equal the target mathematically while representing only part of an item. Add the binary constraint whenever each listed value is either fully included or excluded.

Using a mismatched range: The candidate range and indicator range must have the same number of rows and matching order. If A2 contains 12, B2 must control whether 12 is counted. A shifted range can produce an answer that looks plausible but selects the wrong amounts.

Typing over the total formula: Keep the formula cell intact. Solver should adjust only the decision cells listed in By Changing Variable Cells. If the formula is replaced by a typed number, Solver no longer has a live calculation to optimize.

Expecting an impossible target to work: Some targets cannot be made from the available numbers under your rules. For instance, if all candidate amounts are multiples of 5, a target of 102 cannot be reached using whole items. Solver may correctly report that it could not find a feasible solution.

Tips

Keep the model easy to audit by labeling the candidate values, decision cells, and total. You can add a separate target cell and use it as a reference when checking the answer, but entering 100 directly in the Value Of field is simpler for a fixed target. If the target changes often, storing it in a worksheet cell can make the model easier to maintain, provided you update Solver’s objective setting appropriately.

If you want to find a sum using each candidate at most once, binary indicators are the right model. If items can be used multiple times, use whole-number decision cells instead of binary ones and add limits where needed. For example, if an amount can be selected up to three times, its decision cell needs a whole-number constraint and a maximum of 3. Make sure the worksheet formula still multiplies each amount by its decision quantity.

For a problem where Solver should change the amounts directly rather than choose items from a list, put those adjustable amounts in a variable range and make the objective a formula that adds them. Add constraints for minimums, maximums, whole numbers, or other business rules. This is a different model from selecting a subset, so do not use binary indicators unless the choices really are yes-or-no selections.

Save a copy of the workbook before a substantial Solver run. Solver can change the cells you designate, and preserving the original inputs makes it easier to compare scenarios or restore the starting point. For a large model, write down the objective, variable range, constraints, and solving method so that someone else can understand how the result was produced.

Troubleshooting

Solver is missing from the Data tab: On Windows, check File > Options > Add-ins and enable Solver Add-in as described above. If the add-in is checked but the command still does not appear, close and reopen Excel, then check the add-ins list again. In Excel for Mac, use Tools > Excel Add-ins, select Solver Add-in, and confirm. The exact location of menus can vary slightly by Excel release.

Solver says it cannot find a feasible solution: Check whether a combination can actually reach the target. Then verify that the objective references the total formula, the changing cells are the intended indicators, and the binary constraint applies to the full indicator range. Also check for accidental constraints such as a maximum that is too low or a constraint that excludes a needed item.

The answer contains fractions: Confirm the binary constraint is present and covers every selection cell. If your model allows repeated quantities, use an integer constraint rather than a binary one. Also check whether Solver changed cells outside the intended range; remove any unintended cells from By Changing Variable Cells.

The total does not match what you expected: Recalculate the worksheet and inspect the total formula’s ranges. In the example, the amount range and indicator range must both run from row 2 through row 6. Confirm that the objective points to D2 and that the target is entered as a number, not text. Look for hidden or overlooked rows if you expanded the example.

Solver reports a solution, but the target appears slightly different: Review the formula and the cell’s number format. Formatting can round the displayed value even when the underlying value contains decimals. If your inputs are decimals, decide whether a tolerance is acceptable or whether you need to scale the amounts into whole units, such as cents, before solving. Do not rely on the displayed rounding alone to prove an exact match.

Frequently Asked Questions

Can Solver find a combination of numbers that equals a target?
Yes. Calculate the selected total in a formula cell, assign decision cells to the possible choices, and ask Solver to set the total to the target. Use binary constraints when each number can be selected once or skipped.

Can Solver find more than one valid combination?
Solver returns a solution that satisfies the model, but it does not necessarily list every possible combination. You can keep one solution, add a rule that excludes it, and run Solver again, though the added rule must be designed carefully for the model.

Should I use Goal Seek instead of Solver?
Goal Seek is suitable when one input cell can be adjusted to make a formula reach a target. Solver is a better fit when several cells may change, or when you need constraints such as binary choices, integer values, or upper and lower limits.

Can I use Solver in Excel for the web?
Solver is not available as the built-in Solver Add-in in Excel for the web in the same way as the Windows desktop app. Open the workbook in desktop Excel to configure and run this Solver model.

Will Solver always return the same combination?
Not necessarily. If several combinations meet the target, the returned selection can vary with the model and solving process. If you need a preferred answer, add a clear secondary rule or objective that expresses that preference rather than assuming Solver will choose a particular combination.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.