Product-Mix What-If with SUMPRODUCT

Excel Formulas › Analysis

All versionsSUMPRODUCT

Change how many of each product you sell and watch total profit react. SUMPRODUCT multiplies each product’s margin by its volume and totals the result — a live what-if for your sales mix.

Quick formula: with margins in B and volumes in C:
=SUMPRODUCT(B2:B10, C2:C10)
Each product’s margin times its volume, summed — total contribution. Change any volume and the total updates instantly.

Functions used (tap for the full reference guide):

SUMPRODUCT

The example

Total contribution from a product mix.

ABC
1ProductMarginUnits
2Widget$15400
3Gadget$22250
4Total contribution$11,500

The formula

Total contribution from the mix:

=SUMPRODUCT(B2:B3, C2:C3) // 15×400 + 22×250 = 11,500

How it works

SUMPRODUCT makes the mix a single what-if:

  1. List each product’s margin per unit and the volume you plan to sell.
  2. SUMPRODUCT(margins, volumes) totals margin×volume across all products in one cell.
  3. Change any volume — or add a row — and total contribution updates live: a built-in what-if.
  4. Subtract fixed costs for total profit: =SUMPRODUCT(margins, volumes) − fixed.

Constrained mix? If a resource limits you (machine hours, budget), add a usage column and a check: =SUMPRODUCT(usagePerUnit, volumes) against the limit. For a true optimum under constraints, Excel’s Solver maximizes contribution subject to those limits.

Try it: interactive demo

Live demo

Lines “margin,units”; total contribution updates.

Total contribution:

Variations

Total profit

Less fixed costs:

=SUMPRODUCT(margins, volumes) - fixed

Resource check

Against a limit:

=SUMPRODUCT(hoursPerUnit, volumes)

Revenue mix

Prices instead of margins:

=SUMPRODUCT(prices, volumes)

Pitfalls & errors

Equal-length ranges. Margins and volumes must span the same rows or SUMPRODUCT errors.

Margin, not price. For profit impact use contribution margin per unit, not the sticker price.

Optimization needs Solver. SUMPRODUCT evaluates a mix; finding the best mix under constraints is a Solver job.

Practice workbook

📊
Download the free Product-Mix What-If with SUMPRODUCT practice workbook
A product-mix what-if with SUMPRODUCT, the total-profit, resource-check, and revenue variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I model a product mix in Excel?
Use =SUMPRODUCT(margins, volumes) to total margin×volume across products. Change any volume and the total contribution updates instantly — a built-in what-if.
How do I get total profit from the mix?
Subtract fixed costs: =SUMPRODUCT(margins, volumes) - fixedCosts.
How do I find the best mix under constraints?
SUMPRODUCT evaluates a given mix; to maximize contribution subject to limits (hours, budget), use Excel's Solver add-in.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: SUMPRODUCT formula · Contribution margin · Scenario comparison

Function references: SUMPRODUCT