SUMPRODUCT doesn't stop at two ranges — hand it a third and it multiplies all three together, row by row, before it ever adds anything up.
You handle the import paperwork for a small retailer bringing in a shipment with four different product lines. Each line has its own quantity, unit value in dollars, and duty rate — tariffs vary by what's actually being imported, so no two lines pay the same percentage. In B6, find the shipment's total declared value: every line's quantity times its unit value, all added together. In B7, find the total duty owed: every line's quantity times its unit value times its own duty rate, all added together.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Unit value ($) | Duty rate |
| 2 | Ceramic tile | 1200 | 3.25 | 0.08 |
| 3 | LED fixtures | 340 | 14.5 | 0.12 |
| 4 | Cotton yarn | 800 | 2.1 | 0 |
| 5 | Steel brackets | 560 | 5.75 | 0.1 |
| 6 | Total declared value | |||
| 7 | Total duty owed |
SUMPRODUCT(B2:B5,C2:C5) doesn't total the quantities and then multiply by the total of the unit values — it multiplies each line's own quantity by its own unit value first (1,200 × $3.25, 340 × $14.50, 800 × $2.10, 560 × $5.75) and only adds those four results together, which is what keeps Steel brackets' $5.75 unit value from ever touching Cotton yarn's 800 units. Handing it a third range in B7 doesn't change that pairing, it just extends it one step further: each line's quantity-times-value is multiplied by that same line's own duty rate before anything gets summed, so Cotton yarn's 0% duty zeroes out only its own $1,680 of value instead of dragging down the shipment's average rate, and Steel brackets' 10% applies only to its own $3,220. That per-line multiplication, carried across every array at once, is the entire difference between SUMPRODUCT and summing a column first and multiplying by a rate second.