Using Solver in Excel to Find Values That Sum to a Target

To find which numbers add up to a target in Excel, assign each number a 0-or-1 decision cell, calculate their selected total with SUMPRODUCT, then use Solver to make that total equal the target. In Solver, set the total cell to the target, change the decision cells, and constrain them to be binary; Solver will return a combination that works, if one exists.

What Solver Does for a Sum

Solver is an Excel add-in for finding values that meet a goal while obeying constraints. For a “which of these numbers should I pick?” problem, the numbers themselves stay fixed. A separate decision variable tells Excel whether each number is included: 1 means include it, and 0 means leave it out. Solver searches for a pattern of zeros and ones that makes the selected numbers add to the target.

This is a subset-sum problem, a common kind of selection problem. It is different from simply adding a column, because the desired answer is not known in advance and Excel must choose a subset. It is also different from changing the numbers themselves to reach a target. Solver can handle either type, but the decision variables and constraints need to match the question.

Set Up the Worksheet

Put the candidate values in one column and reserve a neighboring column for Solver’s choices. For example, enter 8, 6, 5, 4, and 3 in A2:A6. Put a starting 0 in each cell from B2:B6. Label the columns “Value” and “Include?” so it is clear which numbers are inputs and which cells Solver can change. Put the desired target, 14, somewhere visible, such as D2.

In a separate cell, calculate the total represented by the choices. With candidate values in A2:A6 and binary decision cells in B2:B6, enter =SUMPRODUCT(A2:A6,B2:B6) in D3. SUMPRODUCT multiplies each value by its matching choice and adds the products. If B2:B6 contains 1, 1, 0, 0, 0, the result is 8×1 + 6×1 + 5×0 + 4×0 + 3×0 = 14.

The formula is linear: each decision cell only multiplies a fixed value. That matters when choosing Solver’s solving method. Keep the candidate values, target, and formula in separate, easy-to-check cells; do not type the answer into the total cell, since Solver needs that cell to calculate the result.

Enable Solver in Excel for Windows

In Excel for Microsoft 365 on Windows, Solver may need to be enabled before it appears on the ribbon. Open File > Options > Add-ins. At the bottom, choose Excel Add-ins in the Manage box, select Go, check Solver Add-in, and select OK. After it loads, look for Solver in the Analysis group on the Data tab.

Solver is a desktop Excel add-in. Excel for the web does not provide the desktop Solver add-in in the same way, so open the workbook in the Excel desktop app to use these steps. On a Mac, enable the add-in through Tools > Excel Add-ins, check Solver Add-in, and confirm; then look for Solver on the Data tab. Ribbon placement can vary slightly by window size or Excel build.

Run Solver to Find a Combination

Select Data > Solver to open the Solver Parameters dialog.
For Set Objective, select D3, the cell containing the SUMPRODUCT total.
Choose Value Of and enter 14, the target in this example.
For By Changing Variable Cells, select B2:B6.
Select Add to create a constraint: set B2:B6 to bin. This tells Solver that each choice must be binary, either 0 or 1.
Choose Simplex LP as the solving method, then select Solve.
When Solver reports a solution, choose Keep Solver Solution to retain the selected values, or Restore Original Values to discard them.

With the example values, one valid answer is 8 + 6 = 14, so Solver can return 1, 1, 0, 0, 0 in B2:B6. Another valid answer is 6 + 5 + 3 = 14, represented by 0, 1, 1, 0, 1. Both are correct. Solver is not promising to find a unique combination or to prefer the one with the fewest values; it needs only to satisfy the objective and constraints.

Why Binary Constraints and Simplex LP Matter

Without the binary constraint, Solver could use fractional choices. For example, it might set a decision cell to 0.5, which means half of a listed item, not a legitimate selection. The bin constraint prevents that. For this setup, Simplex LP is appropriate because the total is a linear formula, while the binary restriction makes the choices whole yes-or-no decisions. Solver’s integer constraints are handled as part of the optimization search.

Do not use GRG Nonlinear merely because the problem contains a constraint. GRG Nonlinear is intended for nonlinear models; this selection model is linear. If your worksheet’s actual formula includes nonlinear operations, the appropriate method may differ. The key is to model the problem accurately rather than changing methods at random when a result looks unexpected.

Variations: Reuse, Counts, and Other Constraints

The basic binary model assumes each listed row can be selected at most once. If a value can be used repeatedly, binary choices are not the right variables. Use nonnegative integer decision cells to represent counts, and add sensible upper bounds so Solver cannot choose an unlimited number of copies. For example, if each item type has at most three available units, constrain its count cell to be an integer between 0 and 3.

You can add constraints to reflect real selection rules, as long as the worksheet formulas express them properly. Examples include limiting the number of selected items, requiring a particular item, or preventing two items from being selected together. A selected-item count can be calculated by summing the binary cells. With decision cells in B2:B6, the count formula is =SUM(B2:B6); you could constrain that count to be no greater than 3. If a specific row must be included, constrain its decision cell to equal 1.

For a target that should be met as closely as possible rather than exactly, the model needs a different objective. One approach is to calculate the difference between the selected total and the target and minimize its absolute value, with an extra formula cell for that difference. If you also require the total not to exceed the target, add that as a separate constraint. Be explicit about whether the goal is exact equality, the closest possible result, or the largest total below a limit; those are different problems.

Check Results and Common Problems

After solving, verify the selected rows independently. Filter or inspect the rows whose decision cells contain 1, add their original values, and confirm that the total equals the target. Keeping the SUMPRODUCT result visible makes this check straightforward. Also retain a copy of the original candidate list, because Solver changes the decision cells when it keeps a solution.

Solver says no feasible solution: Check that the target can actually be made from the available values, that the correct changing cells were selected, and that no extra constraint rules out every combination.
The answer includes fractions: Confirm that the decision range has a bin constraint. A starting value of 0 or 1 does not by itself force a cell to stay binary.
The total is close but not exact: Confirm that the objective uses the correct range and that Value Of is set to the intended target. For decimal inputs, inspect the stored precision and the displayed number format; displayed rounding can hide a difference.
Solver finds a different valid set: Multiple combinations may meet the target. Unless you add a secondary preference, Solver has no reason to choose one particular valid combination.
The model is too large or slow: Reduce unnecessary decision cells and constraints where possible. The standard Excel Solver add-in has a limit of 200 changing variable cells, so a larger selection problem may call for another tool.

Decimal values deserve extra care. If amounts are currency recorded to cents, it can be more reliable to represent them as integer cents—for example, $12.34 as 1234—then solve for an integer-cent target. This avoids some complications from decimal representation and makes exactness easier to inspect. Convert the result back to currency for display, and make sure every input uses the same unit.

Solver Compared with Goal Seek and Formulas

Goal Seek is useful when one input cell can be changed to make one formula reach a target. It does not choose a subset from many fixed items under yes-or-no rules. Solver is the better fit when there are multiple changing cells, selection constraints, or an optimization objective. A direct formula is simpler when the correct combination is already known, but ordinary summation formulas do not generally search all possible subsets for you.

For a small list, manually checking combinations may be quicker and easier to audit. For a longer list, Solver can automate the search, but it still depends on a correct model. If there are more than 200 candidate decision cells, or the model needs features beyond the standard add-in’s limits, consider a purpose-built optimization tool or a programming approach. These alternatives require additional setup and should be checked against the same input data and constraints.

Frequently Asked Questions

Can Excel Solver find numbers that add up to an exact amount?
Yes. Use binary decision cells, calculate the selected total, set that total cell to the target with Value Of, and constrain the decisions to bin. If no allowed combination reaches the target, Solver will not be able to return a feasible exact solution.

Why does Solver select only some of my numbers?
That is the purpose of the 0-or-1 decision cells: 1 includes a row and 0 excludes it. Check that the changing cells cover every candidate and that the binary constraint is applied to that entire range.

Can Solver show every combination that reaches the target?
Solver normally returns a solution, not a complete list of all solutions. If multiple combinations exist, it may return any one of them. Finding every combination requires a repeated search or a separate enumeration method, with each found combination excluded before searching again.

Can I use Solver if the values have decimals?
Yes, provided the model and target use consistent precision. For money recorded to two decimal places, converting amounts to integer cents can make exact comparisons more dependable than relying on displayed rounding.

Is Solver available in Excel for the web?
The desktop Solver add-in is not available in Excel for the web in the same way as in Excel for Windows or Mac. Open the workbook in desktop Excel to enable and run the Solver add-in.

What if I want the closest sum rather than an exact sum?
Change the objective so Solver minimizes the difference between the selected total and the target, and add any required upper or lower limits. State whether totals above the target are allowed, because that changes which answer counts as closest.

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.