SUMPRODUCT for weighted average

Intermediate

When you buy the same material multiple times at different prices, a simple average can be misleading. In your analysis, you usually want a weighted average where larger purchases matter more than smaller ones.

SUMPRODUCT is perfect here because it multiplies two ranges (like price × quantity) and adds the results.

Your task

You’re reviewing purchase orders in A1:D9:

  • Unit price is in C2:C9
  • Quantity is in D2:D9

Fill in the summary cells on the right:

  1. In G2, calculate the total quantity using SUMPRODUCT.
  2. In G3, calculate the total spend (unit price × quantity) using SUMPRODUCT.
  3. In G4, calculate the weighted average unit price:
    • total spend / total quantity

Make sure the price and quantity ranges you multiply are the same size.

Need some help?

Hint 1

SUMPRODUCT can be used with a single range to add values (similar to SUM).

Hint 2

Total spend is the sum of price × quantity for each row: use SUMPRODUCT with the price range and the quantity range.

Hint 3

Weighted average unit price is total spend divided by total quantity. You can reference G2/G3 or calculate both parts directly.

Related function(s)
SUMPRODUCTSUMAVERAGESUMIFSROUND
More exercises
Simple percentage calculationBeginner
AVERAGEIFS multiple criteriaIntermediate
Basic SUMBeginner
Calculate Average SalesBeginner
Back to exercises overview

Exercise