Sigma Code in Excel Solver: Build the Formula and Solve

Formulas & functions · Updated November 21, 2026

If by “sigma code” you mean the Greek capital sigma, Σ, used to show a sum, Excel Solver does not accept sigma notation as code. Put the equivalent Excel formula—often SUM or SUMPRODUCT—in a worksheet cell, then use Solver to maximize, minimize, or set that cell to a target by changing other cells.

What sigma notation means in a spreadsheet

In mathematics, Σ is shorthand for adding a series of terms. For example, the expression Σᵢ cᵢxᵢ means multiply each value cᵢ by its matching value xᵢ, then add the products. In a spreadsheet, that calculation is commonly represented by SUMPRODUCT when the matching values are in two ranges. A plain sum of values in one range is usually represented by SUM.

Solver is an optimization tool, not a place to type mathematical notation or a separate “sigma code.” It reads formulas already entered in the workbook. One cell contains the quantity to optimize, such as total profit or total cost; other cells contain the adjustable decisions; and worksheet formulas calculate the result and any resource usage. Solver searches for decision-cell values that meet the constraints you provide.

This distinction matters: a formula calculates a result for the current inputs, while Solver searches for inputs that produce the best permitted result. If you only need a total, use a formula and do not run Solver. If you need to find the best combination under limits, a formula and Solver work together.

How Solver uses worksheet formulas

Solver needs three parts of a model:

  • Objective cell: A single cell containing the value to maximize, minimize, or set to a specified number.
  • Variable cells: The cells Solver is allowed to change, such as quantities to produce or amounts to allocate.
  • Constraints: Rules that those values must obey, such as a labor limit, a minimum requirement, or nonnegative quantities.

The objective and constraint cells generally contain formulas that depend on the variable cells. Solver changes the variables, recalculates the workbook, and evaluates whether each candidate satisfies the constraints. It then uses a solving method to search for a suitable solution. Solver does not convert a written sigma expression automatically, so first translate the mathematics into valid Excel formulas.

A worked sigma-style example

Suppose a small workshop makes products A and B. Each unit of A contributes $30 and each unit of B contributes $20. A uses two labor hours and one machine hour; B uses one labor hour and two machine hours. The workshop has 100 labor hours and 80 machine hours available. The goal is to choose nonnegative production quantities that maximize total contribution.

Enter the decision quantities in B2:C2, with A in B2 and B in C2. Enter their contribution values in B3:C3, with 30 in B3 and 20 in C3. Put the objective formula in D3. With quantities in B2:C2 and per-unit contributions in B3:C3, D3 can calculate the total using:

=SUMPRODUCT(B2:C2,B3:C3)

For the resource calculations, enter labor use per unit in B6:C6 as 2 and 1, and machine use per unit in B7:C7 as 1 and 2. Put available labor, 100, in E6 and available machine hours, 80, in E7. With quantities in B2:C2 and labor coefficients in B6:C6, D6 calculates labor used with:

=SUMPRODUCT($B$2:$C$2,B6:C6)

With quantities still in B2:C2 and machine coefficients in B7:C7, D7 calculates machine hours used with:

=SUMPRODUCT($B$2:$C$2,B7:C7)

In sigma notation, the objective is the sum of each product’s contribution multiplied by its quantity. The two resource formulas have the same structure, using resource consumption per unit instead. The dollar signs in the resource formulas keep the decision-cell range fixed if the formula is copied elsewhere; the coefficient range remains relative.

The best continuous solution is 40 units of A and 20 units of B. It uses 100 labor hours and 80 machine hours, so both limits are met exactly. Its contribution is 40 × $30 plus 20 × $20, which equals $1,600. If the business requires whole units, add integer constraints; in this example the optimum is already whole-numbered.

Set up Solver in Excel for Microsoft 365

On Excel for Microsoft 365 for Windows, enable the Solver Add-in if the Solver command is not visible. Go to File > Options > Add-ins. At the bottom, choose Excel Add-ins in the Manage box and select Go. Check Solver Add-in and choose OK. Solver should then appear on the Data tab, usually in the Analysis group.

To configure the example, go to Data > Solver and enter the following:

  1. Set Objective to D3, the cell containing total contribution.
  2. Choose Max because the goal is to maximize contribution.
  3. Set By Changing Variable Cells to B2:C2.
  4. Add the constraints D6 <= E6 and D7 <= E7.
  5. Ensure the decision quantities cannot be negative. You can add B2:C2 >= 0, or use Make Unconstrained Variables Non-Negative if that option is selected.
  6. Choose a solving method, then select Solve. Review the result and choose Keep Solver Solution if it is acceptable.

For this linear example, select Simplex LP. Solver may offer to keep the solution or restore the original values. Keeping the result places the selected quantities in the variable cells, and the formulas recalculate the objective and resource usage. Save the workbook if you want to preserve the result and model.

Choose the right solving method

The solving method should match the mathematics in the model. Simplex LP is appropriate when the objective and constraints are linear in the changing cells, as in the product example. SUMPRODUCT does not automatically make a model linear or nonlinear; what matters is how the formula depends on the decision variables. A sum of fixed coefficients multiplied by decision quantities is linear.

GRG Nonlinear is intended for smooth nonlinear models, such as objectives involving products of decision variables or certain nonlinear formulas. Evolutionary can handle more irregular or discontinuous models, but may take longer and can return a solution that is not as strong as a global optimum. No method can fix a model whose formulas or constraints do not represent the real problem. If a model is nonlinear, check Solver’s result and settings carefully rather than assuming that a reported solution is the only possible optimum.

Constraints, whole numbers, and practical assumptions

Constraints express the limits that make an optimization model useful. A “less than or equal to” constraint is suitable for a resource cap; an equality may represent an exact requirement; and a “greater than or equal to” constraint can set a minimum. In the example, total labor and machine usage must each remain at or below the available amount.

Real decisions may need additional rules. If production must be in whole units, constrain the quantity cells as integer. If a decision is yes or no, a binary constraint may be appropriate. If product A must be made at least 10 units, add a lower-bound constraint. These rules change the feasible solutions, so include them only when they reflect the actual situation. For example, a continuous solution of 3.6 units is not a practical production plan if fractional units cannot be made.

Also check whether the model represents all relevant limits. The example has no demand cap, setup cost, storage limit, or minimum production commitment. Solver cannot infer those conditions. If one applies, it must be represented in the worksheet and added as a constraint, or the calculated optimum may be mathematically correct but unsuitable for the real decision.

Common Solver and sigma-formula problems

  • Solver says it cannot find a feasible solution: Check for conflicting constraints, incorrect comparison directions, and formulas that point to the wrong cells. Temporarily review the model with a simple set of values to see whether the limits can be met at all.
  • The objective does not change: Confirm that the objective formula refers, directly or indirectly, to the changing cells. If it uses fixed numbers instead, Solver has nothing meaningful to optimize.
  • SUMPRODUCT returns an unexpected result: Verify that the two ranges have the same dimensions and that coefficient cells contain numbers rather than text. Confirm that each coefficient is aligned with the intended decision variable.
  • Solver returns fractional quantities: Add integer constraints if the quantities must be whole numbers. Nonnegative values alone do not require integers.
  • The answer seems implausibly large: Check units, omitted upper bounds, and whether a resource constraint was left out. A maximum objective can grow without limit if the model allows quantities to increase indefinitely.

A useful check is to calculate the reported objective and resource totals independently from the displayed quantities. In the example, quantities of 40 and 20 should produce contribution of $1,600, labor use of 100, and machine use of 80. If the worksheet does not show those values, inspect the formulas and cell references before relying on the result.

Solver versus ordinary formulas

SUM and SUMPRODUCT answer questions about values already present in cells. Solver answers a different question: which values should go in selected cells to make an objective as high or low as possible while respecting the modeled rules? The sigma symbol describes a mathematical sum; the Excel formula implements that sum; Solver performs the search over adjustable values.

For a straightforward total, a formula alone is simpler and more transparent. For a planning problem with multiple adjustable choices and constraints, Solver can be useful. For recurring large-scale optimization or specialized requirements, a dedicated optimization system or a custom program may be more suitable. The right choice depends on model size, complexity, and how often the problem must be solved.

Availability and platform notes

These instructions target Excel for Microsoft 365 on Windows. In Excel for Microsoft 365 for Mac, Solver is also an add-in, but its setup and dialog presentation can differ slightly; use Tools > Excel Add-ins to manage add-ins, then look for Solver on the Data tab after enabling it. Excel for the web does not provide the desktop Solver Add-in for running a Solver model, so use the desktop Excel application to configure and solve it. A workbook can still contain formulas and saved results that are viewed in the web version.

If you share a workbook, include clear labels, units, the objective formula, and a description of important constraints. A colleague should be able to tell which cells are decisions and why the selected result is allowed. Solver’s output is only as dependable as the data and assumptions in the worksheet.

Frequently Asked Questions

Can I type Σ directly into Excel Solver?
No. Solver expects a worksheet objective cell and changing cells, not sigma notation. Enter the sum as an Excel formula, then select that result cell as the objective.

Which Excel formula is closest to sigma notation?
It depends on the expression. SUM adds values in a range, while SUMPRODUCT multiplies corresponding values in two or more ranges and adds the products. Choose the function that matches the mathematical expression.

Does Solver automatically create the sum formula?
No. Build the objective and any constraint calculations in worksheet cells first. Solver changes the decision cells and evaluates those formulas; it does not write the model for you.

Why does Solver allow decimal answers?
Decision cells can take decimal values unless the model restricts them. Add an integer constraint when whole units are required, or a binary constraint for yes-or-no decisions.

Can Excel for the web run Solver?
The desktop Solver Add-in is not available for solving models in Excel for the web. Open the workbook in desktop Excel for Microsoft 365 to set up and run Solver.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.