Math & Trig
Bundle and quote pricing with SUMPRODUCT (quantities, unit prices, and discounts)
Compute gross subtotals, line-discounted net totals, and total savings in a single cell with SUMPRODUCT—no line-extension column required.
Multiply Qty * UnitPrice * (1 - DiscPct) row-by-row in memory
Compact quote summaries often list Quantity, Unit Price, and Line Discount % without room for an extra Extended Total column.
SUMPRODUCT(B2:B4, C2:C4) computes the gross dot product (10*25 + 4*50 + 1*300 = $750), while SUMPRODUCT(B2:B4 * C2:C4 * (1 - D2:D4)) applies each line's individual discount percentage before summing ($225 + $160 + $300 = $685).
Use numeric quantities and prices with discounts between 0 and 1. This example rounds the final total; rounding each line first may produce a different result. Taxes, fees and negative adjustments require their own policy.
| Item (A) | Qty (B) | UnitPrice (C) | DiscPct (D) | Net Line Value |
|---|---|---|---|---|
| Seat | 10 | 25 | 0.1 | 225 |
| Addon | 4 | 50 | 0.2 | 160 |
| Onboarding | 1 | 300 | 0 | 300 |
Net bundle total, gross subtotal, and total dollar discount
Total dollar savings across the quote equals SUMPRODUCT(B2:B4 * C2:C4 * D2:D4) ($25 + $40 + $0 = $65).
=ROUND(SUMPRODUCT(B2:B4 * C2:C4 * (1 - D2:D4)), 2)Gross subtotal before discounts
SUMPRODUCT(B2:B4, C2:C4) totals $750 ($685 net + $65 discount).
=SUMPRODUCT(B2:B4, C2:C4)Try the example
Use the original values or change them to test your understanding.