Excel Solver: Find Values That Add Up to a Target Sum

To make selected values add to a target, put the numbers in one column, binary 0/1 choices beside them, calculate their weighted sum with SUMPRODUCT, then use Data > Solver to set that total to the target while changing the choices. Add a binary constraint to the choice cells so Solver includes or excludes each number instead of using fractions.

What Solver is finding

This is a subset-sum problem: from a list of available numbers, choose a combination whose total matches a specified amount. For example, given 12, 7, 19, 24, 5, and 11, you might want to know which numbers total 36. Excel’s ordinary SUM function adds numbers you already selected; it does not decide which cells to include. Solver searches for the choices that satisfy the total.

The 0/1 choice cells represent those decisions. A 1 means include the corresponding value; a 0 means leave it out. Multiplying each source value by its choice and adding the products gives the selected total. A binary restriction matters: without it, Solver could use partial amounts such as 0.5 of a value, which usually is not what a selection problem asks for.

Solver can also handle related questions, such as choosing quantities of products, meeting a total while respecting limits, or minimizing cost subject to a required sum. The setup depends on whether each item can be chosen once, chosen repeatedly, or used in a fractional amount.

Prepare the worksheet

Use this small example to build the model. Enter the available values in A2:A7 and put a starting 0 in each decision cell in B2:B7. In C2, calculate the selected total. With values in A2:A7 and binary decisions in B2:B7, enter this formula in C2:

=SUMPRODUCT(A2:A7,B2:B7)

Set the target to 36. A solution is 12, 19, and 5: the corresponding decision cells become 1, and the others remain 0. The arithmetic is 12 + 19 + 5 = 36. The same model works with a longer list: extend both ranges in the formula and include the added decision cells in Solver’s changing-cell range.

Keep the source values and decision cells aligned row by row. If A4 contains 19, B4 must be the decision for that 19. Labels in a separate column can make the result easier to understand, especially when the values are product names, invoice amounts, or task durations rather than anonymous numbers.

Enable Solver in Excel for Windows

Solver is an Excel add-in. In Microsoft Excel for Microsoft 365 on Windows, enable it if the Data tab does not show Solver:

Choose File > Options > Add-ins.
At the bottom, set Manage to Excel Add-ins and choose Go.
Select Solver Add-in, then choose OK. If prompted, allow Excel to install or load it.

After it is enabled, open the Data tab and choose Solver, usually in the Analysis group. In Excel for Mac, enable the add-in through Tools > Excel Add-ins, select Solver Add-in, and then open Solver from the Data tab. Solver is not available to run as this desktop add-in in Excel for the web; open the workbook in the desktop application to solve it.

Set up Solver to match the target

With the example layout—total in C2, decisions in B2:B7, and a target of 36—open Data > Solver and enter the model parameters:

Set Objective: select $C$2.
Choose Value Of and enter 36. This asks Solver to make the objective cell equal the target, rather than maximize or minimize it.
By Changing Variable Cells: select $B$2:$B$7.
Choose Add, select $B$2:$B$7 as the cell reference, and choose bin as the constraint type. This makes each decision cell binary.
Choose Simplex LP as the solving method, then choose Solve.

When Excel reports a solution, choose Keep Solver Solution to retain the selected 0s and 1s. If you choose Restore Original Values, the worksheet returns to its starting decision values. The objective cell should show 36, and the selected rows should add to that number.

Simplex LP is appropriate because the total is a linear calculation and the decision cells have binary restrictions. Solver can handle these integer restrictions as part of a linear model. Do not choose GRG Nonlinear merely because the decision cells are restricted to whole values; the model itself is still linear.

Adapt the model to different rules

The binary setup assumes each listed item is either chosen once or not chosen. Change the variable type or add constraints when the real problem differs:

Allow a quantity of each item: Use integer decision cells instead of binary ones, and add lower or upper bounds if needed. For example, a decision can be 0, 1, 2, or 3 units.
Allow fractional amounts: Do not use a binary or integer restriction. This is suitable for divisible quantities, but Solver may choose fractions.
Limit the number of selections: Add a separate count calculation for the decision cells and constrain it to a maximum. For a simple binary list, the count is the sum of the choices.
Require a particular item: Set its decision cell to 1 with a constraint. To prohibit an item, constrain its decision cell to 0.
Choose the cheapest combination: Calculate total cost using another weighted sum, then minimize the cost while keeping the value total at or above the required amount.

For a cost-minimization model, the objective should be the cost calculation, not the value total. The value total becomes a constraint. This distinction lets Solver search for the least expensive valid selection rather than any combination that reaches the target.

Exact matches, multiple answers, and limits

There may be more than one valid combination. Solver returns a feasible solution, not necessarily the only solution, and a different starting point or model may lead to another valid selection. If a particular answer is preferred, encode the preference as an objective or an additional constraint. For instance, you can minimize the number of selected items while requiring the total to equal the target.

There may also be no exact match. If the available values are whole numbers and the target falls between all reachable totals, an equality constraint cannot be satisfied. Consider whether the real requirement is a minimum, a maximum, or a tolerance around the target. Those are different business rules and should be modeled explicitly rather than accepting a near match by accident.

The standard Solver add-in has a limit of 200 changing variable cells. Larger selection problems may exceed that limit or take a long time, since the number of possible combinations grows rapidly with the list size. For a large model, consider a specialized optimization add-in or another optimization tool, and verify that it supports the constraints your problem needs.

Common problems and checks

If Solver says it cannot find a solution, check the model before concluding that the target is impossible. A wrong range, an unintended blank row, or a decision cell outside the binary constraint can change the problem. Confirm that the formula and the changing-cell range cover exactly the same rows, and inspect the target value and any extra limits.

The result is a fraction: Confirm that the decision range has a bin constraint. If quantities should be whole numbers but can exceed one, use an integer constraint and suitable bounds instead.
The total is not the target: Check whether you chose Value Of and typed the target, rather than selecting Max or Min. Confirm that the objective points to the total cell.
Solver produces an unexpected selection: Check that each number is paired with the correct decision cell, and that no value is stored as text or omitted from the formula range.
The total is close but not exact: Review the input precision and the intended tolerance. Rounded display formatting can hide decimals stored in cells; formatting a number does not change its underlying value.

For a model involving decimals, decide how exact the requirement should be. A target displayed as 10.00 might have more decimal places stored behind the display. If a small difference is acceptable, model a permitted range with constraints rather than relying on how the cells appear on screen.

When a formula or Goal Seek is enough

Use SUM when you already know which cells to add, or SUMIF when you need to total values matching a condition. Those functions calculate a total from known data; they do not search for a combination of rows that reaches a chosen target. Solver is the more appropriate built-in tool when it must choose several decision cells under rules.

Goal Seek is different: it changes one input cell to make one formula reach a specified result. It can be useful when a single variable is adjustable, such as finding one missing amount in a simple calculation. It cannot choose a subset from many independent values, and it does not enforce a binary selection across a range. For a multi-cell choice problem, use Solver.

For very small lists, manually checking combinations may be quicker than setting up Solver. For a repeatable process, however, the Solver model makes the selection rules visible and easier to revise. Keep the source list, decision cells, formula, and target clearly labeled so another person can audit what the result means.

Frequently Asked Questions

Can Excel Solver find numbers that add up to a specific amount?
Yes. Set a total formula as the objective, choose Value Of, enter the target, and make the selection cells the changing variables. Add a binary constraint when each source value can be selected only once.

Why does Solver choose part of a number?
By default, a changing cell may be allowed to contain a fraction. Add a bin constraint for yes-or-no choices, or an integer constraint when whole-number quantities greater than one are allowed.

Can Solver use the same value more than once?
Not with one binary decision cell for each row: that model can select each listed row at most once. To allow repeats, model a quantity for the item and constrain it to a nonnegative integer, with an upper bound if the number of copies is limited.

What if no combination equals my target?
Solver cannot produce an exact feasible match if the model has no combination satisfying the equality. Check for mistaken ranges or constraints, then decide whether the requirement should instead be a minimum, maximum, or permitted interval.

Can Excel for the web run Solver?
The Solver add-in described here is for desktop Excel. Open the workbook in Excel for Windows or Mac to configure and run Solver; do not assume that viewing a workbook in a browser means its Solver model can be run there.

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.