Spreadsheet Practice

Lesson 24 of 29, Analysis

SUMPRODUCT

Multiply row by row and add it up, in one cell.

Pro9 minutes, 3 exercisesSUMPRODUCT

Watch it done

1 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

SUMPRODUCT(B2:B7,C2:C7) multiplies units by price on every row and adds the results: total revenue with no helper column.

Multiply in a condition like (D2:D7="Hardware") and its TRUE/FALSE values become 1s and 0s that switch rows on and off. Divide by SUM of the weights for a weighted average.

Syntax

=SUMPRODUCT(B2:B7,C2:C7)Total revenue
=SUMPRODUCT(B2:B7,C2:C7)/SUM(B2:B7)Weighted average price
=SUMPRODUCT((D2:D7="Hardware")*B2:B7*C2:C7)Revenue for one category

Watch out for

  • TRUE/FALSE only become 1/0 when you do math on them, so multiply the condition by a range.
  • All ranges must be the same size.

The exercises

  1. Total revenue. In G2, calculate total revenue (units × price for every row) in one formula.
  2. Weighted average price. In G3, calculate the average price per unit sold: total revenue divided by total units.
  3. Revenue for one category. In G4, calculate revenue for the Hardware category only, using SUMPRODUCT.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Total revenue
  2. Weighted average price
  3. Revenue for one category
Try these functions in the Formula Lab