Excel Solver: Find Numbers That Sum to a Value?

Yes. Excel Solver can choose numbers from a list that add up to a target: give each number a 0-or-1 decision cell, make the sum of the selected numbers equal the target, and use Solver to find a solution. This works well for subset-selection questions such as choosing which invoices, weights, or items make a required total.

What Solver does—and what it does not do

Solver is an Excel add-in that changes selected cells to meet conditions you set. For a sum-from-a-list problem, the key is to model each list item as either selected or not selected. A decision value of 1 means include the corresponding number; 0 means leave it out. Solver then searches for a combination whose total matches the target.

This is different from asking Excel to invent any numbers that add up to a value. If numbers can be any real values, there are usually many possible answers, and Solver needs additional rules—such as minimums, maximums, or a required count—to identify a useful one. The binary setup in this guide is specifically for selecting from a fixed list.

Solver does not normally list every possible combination. It finds a solution that satisfies your constraints and objective. If more than one combination works, the result can depend on the objective and the search process.

Build the selection model

Arrange the source numbers in one column and put a decision cell beside each one. A separate formula cell calculates the selected total, and another calculates how many items are selected. Put the target in a cell of its own. For example, use A2:A7 for numbers, B2:B7 for decision cells, D2 for the selected total, E2 for the number of selections, and H1 for the target.

With the numbers in A2:A7 and the corresponding 0-or-1 decisions in B2:B7, enter this formula in D2 to total only the selected numbers: =SUMPRODUCT(A2:A7,B2:B7)

With the decision cells in B2:B7, enter this formula in E2 to count how many numbers are selected: =SUM(B2:B7)

SUMPRODUCT multiplies each number by its decision value and adds the products. A number paired with 0 contributes nothing; a number paired with 1 contributes its full value. The count formula provides a useful objective: minimizing it asks Solver to use as few numbers as possible while still reaching the target.

Set up Solver in Microsoft 365 for Windows

If Solver is not already available, enable the add-in first. In Excel for Microsoft 365 on Windows, select File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, select Go, check Solver Add-in, and select OK. Solver should then appear on the Data tab. Excel for the web does not run the Solver add-in; use the desktop application. In Excel for Mac, enable Solver through Tools > Excel Add-ins, then open Solver from the Data tab. Menu details can vary slightly by release.

With the worksheet laid out as above, select Data > Solver and configure the model:

Set Objective to E2, and choose Min. This tells Solver to minimize the number of selected items.
Set By Changing Variable Cells to B2:B7.
Add a constraint requiring D2 to equal H1, so the selected total must match the target.
Add a constraint requiring B2:B7 to be binary. In the constraint dialog, choose the bin relation for the decision cells.
Choose Simplex LP as the solving method, then select Solve.

The model is linear: it uses sums and products of fixed list values by decision variables. The binary restriction means each decision must be 0 or 1. Solver handles this as an integer-constrained model. When it reports a solution, choose Keep Solver Solution to retain the values, or restore the original values if you do not want to keep them.

Worked example: choose numbers that total 80

Suppose A2:A7 contains 12, 19, 27, 34, 41, and 53, and H1 contains the target 80. Start with zeros in B2:B7. The formula in D2 calculates the total represented by those decisions, while E2 counts selected entries. After setting the equality and binary constraints, Solver can return 1 beside 27 and 53, and 0 beside the other numbers.

The selected total is 27 + 53 = 80, and the selection count is 2. Because the objective is to minimize E2, Solver seeks a feasible solution with the fewest selected numbers, rather than merely any solution. In this example no single listed number equals 80, so a solution cannot use just one item; the two-item combination reaches the target.

For a different task, change the objective. For instance, minimizing the count is useful when you want the simplest combination, but it may not be appropriate if you need to use a particular number of items or prefer certain entries. You can add another constraint, such as requiring the count cell to equal a chosen number, or use a separate score column and define an objective that reflects your priority.

Exact totals, decimals, and negative values

For whole numbers, a direct equality constraint is usually straightforward. Decimal amounts can be more delicate because spreadsheet calculations use finite-precision numeric values, and a Solver solution may be affected by numerical tolerances. If the data represent cents, one practical approach is to convert every amount and the target into whole cents before solving. For example, $12.34 becomes 1,234 cents. This avoids asking Solver to match decimal currency values directly, provided every amount has no more than two decimal places.

With amounts in dollars in A2:A7, decisions in B2:B7, and two-decimal amounts that you want to express as cents, put this formula in D2: =SUMPRODUCT(ROUND(A2:A7*100,0),B2:B7)

In this cents-based layout, put the target expressed in cents in H1 and constrain D2 to equal H1. The formula rounds each amount to the nearest cent before multiplying by its decision. Check that this rounding matches the way your source data should be treated; it should not be used to conceal meaningful precision beyond cents.

Negative values are allowed in the list, but they can make the search less intuitive. A positive and a negative entry may cancel one another, so a target could be reached by combinations that are not obvious. If negative entries are not valid for the real task, exclude them or add constraints that reflect the actual rules rather than relying on the solver to infer them.

Choose an objective that matches the real question

The target equality alone says what total is required, but it does not say which valid combination is best. Add an objective or extra rules when the choice matters:

Use the fewest items: minimize the sum of the binary decision cells.
Use a fixed number of items: constrain the decision-cell sum to that number.
Prefer lower-cost choices: calculate a cost for the selected items and minimize that cost, while keeping the target-total constraint.
Require or forbid an item: constrain its decision cell to 1 or 0.
Limit a category: create a separate total for that category and constrain it as needed.

These rules should describe the actual decision you are trying to make. Without them, Solver cannot know that one combination is preferable to another. Also, minimizing the number selected does not automatically minimize cost, risk, waste, or any other measure; those require their own objective or constraints.

Common problems and practical limits

If Solver says it cannot find a feasible solution, first check the arithmetic and the model. Confirm that the target is actually reachable from the available entries, that the formula points to the correct ranges, that the target cell contains a number, and that every changing cell is included in the binary constraint. A misspelled or misplaced range can make Solver optimize the wrong cells.

Another common issue is leaving the decision cells unrestricted. If they are allowed to take fractional values, Solver might use part of an item—for example, 0.5 of a 20—to reach a target of 10. That is mathematically valid for continuous variables but wrong when each listed item must be either used whole or not used. The binary constraint prevents that outcome.

The standard Solver add-in has a limit of 200 adjustable cells, so a list with more than 200 decision variables may exceed its capacity. Large lists can also take longer because the number of possible subsets grows rapidly. Solver is not a guarantee that every difficult model will be solved quickly. Keep the model as small as practical, remove entries that cannot be used, and make constraints as clear as the real problem allows.

If the target is an approximate total rather than an exact one, do not use an equality constraint. Instead, model the acceptable range with lower and upper bounds—for example, require the selected total to be at least a minimum and no more than a maximum. This is more appropriate when measurements are rounded or the task allows a tolerance.

When to use another approach

For a short list and one target, Solver is convenient and lets you add business rules without writing code. For a very large list, many repeated targets, or a need to display all combinations, a dedicated subset-sum or optimization tool may be a better fit. A formula that simply totals the list cannot by itself decide which entries to include; the selection decisions must be represented somewhere.

For a small number of entries, you can also inspect combinations manually or create a helper table, but the number of possible subsets doubles with each additional item. That quickly becomes impractical. If you use a macro or an external script instead, validate its output against the original values and rules. Regardless of the method, keep a copy of the source list and check that the selected entries really add to the required target.

Frequently Asked Questions

Can Excel find which numbers add up to a specific number?
Yes. Use binary decision cells for the available numbers, calculate their selected total with SUMPRODUCT, and use Solver to constrain that total to the target. Add an objective such as minimizing the number of selected entries if you need a preferred solution.

Can Solver find all combinations that reach the target?
Solver is designed to find a solution to an optimization model, not automatically enumerate every matching combination. To collect all solutions, you need a repeatable process that excludes each found combination and solves again, or a tool designed to enumerate subsets. Save each result before running another solve.

Why did Solver select half of a number?
The decision cells were likely allowed to be fractional. Add a binary constraint to the entire decision range. Binary variables are restricted to 0 or 1, which represents not selected or selected.

Can Solver choose numbers if some values are negative?
Yes, provided the model and constraints allow them. Negative values can combine with positive values to reach a target, so review the selected entries and add limits if the real task rules out certain combinations.

Does Solver work in Excel for the web?
The Solver add-in is used in the Excel desktop application, not Excel for the web. Open the workbook in desktop Excel to enable and run Solver. Excel for Mac also has a Solver add-in, though its enabling menu is different from Windows.

What if I want a total close to the target, not exactly equal?
Replace the equality requirement with lower and upper bounds for the selected total, or set an objective that minimizes the difference from the target. Choose bounds or an objective that match the acceptable tolerance for your task.

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.