Excel Solver: Find Combinations That Sum to a Target

Excel does not have a built-in worksheet function named Solver; Solver is an add-in that can search for a combination of numbers that equals a target. Put a 0-or-1 decision variable beside each number, calculate the selected sum, then use Data > Solver to set that sum to the target and constrain the decision variables to be binary.

What Solver does—and what it does not do

Solver is an optimization tool, not a formula you type into a cell. It changes values in selected cells to make an objective cell reach a specified result, while respecting constraints you provide. For a combination-sum problem, each candidate amount gets a decision cell: 1 means include the amount, and 0 means leave it out. A total calculated from those choices is what Solver matches to the target.

This is different from asking Excel to add every value in a range. SUM can calculate a total when you already know what to include; Solver searches for the included values. It is also different from a lookup, which retrieves a matching record rather than choosing an arbitrary subset. Solver can find a valid subset, but it does not automatically return a neatly formatted list of every possible subset.

There can be more than one answer. For example, if candidates include 2, 3, 4, and 5 and the target is 7, both 2 + 5 and 3 + 4 work. Solver normally returns one solution at a time, so the result may depend on its search and starting values.

Set up a small combination-sum example

Suppose the available amounts are 12, 18, 25, 31, and 40, and you need a subset totaling 56. Put the candidate values in A2:A6, with one value per row. Put a 0 in each of B2:B6 to start; these cells will become the decision variables. Enter the target, 56, in D2.

Use a total cell to multiply each candidate by its selection flag and add the products. With candidates in A2:A6 and binary decision cells in B2:B6, enter =SUMPRODUCT(A2:A6,B2:B6) in D3. Before Solver runs, this returns 0 because all the flags are zero. When Solver finds the answer, D3 will return 56. The chosen values in this example are 25 and 31, so the corresponding flags become 1 and the other flags remain 0.

The layout might look like this:

A2:A6: candidate values, 12, 18, 25, 31, and 40.
B2:B6: selection flags, initially all 0.
D2: target value, 56.
D3: selected total, calculated by SUMPRODUCT.

Keeping the values and flags in separate columns makes the model easy to audit. You can see which items were selected without changing the original list, and you can check the final sum directly.

Enable Solver in Excel for Microsoft 365

On Excel for Microsoft 365 for Windows, Solver may need to be turned on once before it appears on the ribbon. Choose File > Options > Add-ins. At the bottom, next to Manage, choose Excel Add-ins and click Go. Select Solver Add-in, then click OK. If prompted to install it, allow Excel to do so. The Solver command should then appear on the Data tab, usually in the Analysis group.

On Excel for Mac, open Tools > Excel Add-ins, select Solver Add-in, and choose OK. Once enabled, look for Solver on the Data tab. Excel for the web does not provide the desktop Solver add-in interface, so open the workbook in the Windows or Mac desktop app to run this method. The exact appearance of the ribbon can vary with window size and Excel updates.

Run Solver to find an exact sum

With the example sheet prepared, open Data > Solver. Configure the model as follows:

Set the Objective to D3, the cell containing the selected total.
Choose Value Of and enter 56, the target amount.
In By Changing Variable Cells, enter B2:B6.
Add a constraint for B2:B6 and choose bin, meaning binary.
Choose Simplex LP as the solving method, then click Solve.

For this setup, Simplex LP is suitable because the total is a linear calculation and the decision variables are restricted to binary values. The binary constraint is essential: without it, Solver could use fractions such as 0.5 of one amount, which is not a legitimate selection when each item must be either included or excluded.

If Solver reports a solution, choose Keep Solver Solution to retain the flags it found. Verify that D3 equals 56 and inspect the rows marked 1. In this example, the selected rows should show 25 and 31. If Solver cannot find an exact solution, it may report that it could not find a feasible solution under the constraints; that means no combination satisfies the model as configured, or there is an issue in the setup.

Find the closest total when an exact match is impossible

Sometimes no subset equals the target, but the closest total is useful. Add a difference cell. With the calculated total in D3 and target in D2, enter =ABS(D3-D2) in D4. Before Solver runs, this returns the distance between the current total and target; after it runs, a result of 0 means an exact match. For example, if the selected total is 55 and the target is 56, D4 returns 1.

In Solver, set the objective to D4 and choose Min, rather than setting the objective total to a fixed value. Keep B2:B6 as the changing cells and retain the binary constraint. Solver then searches for the subset that minimizes the absolute difference. If multiple subsets tie for the smallest difference, it may return any one of them. This model answers a different question from an exact-sum model: it permits a nonmatching total and prioritizes closeness.

You can also add practical rules. For instance, a constraint can require a minimum or maximum number of selected items by adding a separate count cell. With binary flags in B2:B6, put =SUM(B2:B6) in E2; this returns the number of selected candidates. You could then constrain E2 to be less than or equal to 3, or set it equal to 2. State the layout first: this count formula assumes the decision flags are in B2:B6.

Common setup problems and limits

Several issues explain many unsuccessful Solver runs:

The binary constraint is missing. Solver may choose fractional quantities instead of a true subset. Set B2:B6 to bin in the constraints.
The wrong cell is the objective. The objective should be the calculated total for an exact target, or the absolute difference for a closest-total search—not the target input cell.
The formula does not cover every candidate. Check the SUMPRODUCT ranges and make sure they have matching dimensions. Here, A2:A6 and B2:B6 both contain five cells.
Values are stored as text. Text that looks like a number may not behave as expected in calculations. Check the source cells and convert them to numeric values if necessary.
The target is impossible under additional rules. A minimum item count, maximum item count, or required inclusion can rule out an otherwise valid combination.

The standard Excel Solver add-in supports up to 200 changing variable cells. A model with more candidates can exceed that limit, and a very large candidate list can also make the search take longer. If the problem is large, has many extra conditions, or requires every possible combination, a dedicated optimization tool or a programmatic approach may be more suitable. Keep a copy of the workbook before changing a complex model, and note the constraints so the result can be checked later.

Solver compared with formulas and other approaches

Solver is a practical choice when you have a moderate list and want one subset that meets a target, especially when you need extra rules such as limiting the number of selected items. It is also convenient for a one-off search because the candidates stay visible in the worksheet and the model can be adjusted through a dialog.

A normal formula is better when the desired combination is already known, or when you want to calculate the total for a fixed selection. A formula-only method can be built to enumerate subsets, but the number of possible subsets doubles with every additional candidate: 20 items have 1,048,576 possible subsets. That growth can make exhaustive formula approaches cumbersome. Solver searches for a solution rather than listing the full set of possibilities.

For larger or repeatable tasks, users may consider an optimization package, a script, or a database workflow. Those options can be better for automation, very large models, or returning all solutions, but they usually require more setup than Solver. Power Query is useful for importing and reshaping candidate data; it is not, by itself, a general-purpose subset optimizer.

How to check and document the result

Do not rely only on Solver’s completion message. Review the selected rows and calculate their total independently. In the example, the flags should identify 25 and 31, and their sum is 56. If the data represents quantities, costs, inventory, or another consequential measure, confirm units, rounding, duplicate entries, and any real-world restrictions before acting on the result.

Save the workbook with the candidate list, decision flags, objective formula, target, and constraints intact. Solver’s changing cells show the current solution, but a future user also needs to know what was allowed. Add a brief note describing whether the goal was an exact sum or the closest possible sum and whether selection-count or required-item rules were applied.

Frequently Asked Questions

Is Solver a function in Excel?
No. Solver is an Excel add-in that searches for values satisfying an objective and constraints. You use worksheet formulas, such as SUMPRODUCT, to calculate the total that Solver changes.

Can Excel Solver find all combinations that add to a number?
Solver normally returns one solution per run, not a complete list of every possible subset. You can rerun it with additional restrictions to search for another result, but systematically listing all combinations is usually better handled by a purpose-built script or tool.

Why does Solver choose part of an item?
The decision cells are probably not constrained as binary. Add a bin constraint for the full range of selection cells so each can be only 0 or 1.

Can Solver find a combination with negative or decimal values?
It can work with numeric candidates, including negative or decimal values, but the model must match the intended rules. For example, binary flags still mean each candidate is either selected once or not selected; use different variable constraints if repeated quantities are allowed.

Why is Solver missing from the Data tab?
The Solver Add-in may not be enabled. On Windows, activate it through File > Options > Add-ins > Excel Add-ins > Go. On Mac, use Tools > Excel Add-ins. The desktop add-in is not available through the Excel for the web interface.

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.