Basic FunctionsAdvanced

Total a multi-line customs declaration with a three-array SUMPRODUCT

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.

Task:

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.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

7 rows × 4 columns2 cells you fill in
ABCD
1ItemQtyUnit value ($)Duty rate
2Ceramic tile12003.250.08
3LED fixtures34014.50.12
4Cotton yarn8002.10
5Steel brackets5605.750.1
6Total declared value
7Total duty owed
What this exercise teachesMay contain the answer

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.