Lessons › Excel & Google Sheets
Excel & Google Sheets lesson
SUMPRODUCT
2 × 35 + 1 × 15 + 3 × 8 + … = 459. No extra column needed.
Try this lesson free →Example
SUMPRODUCT multiplies Qty × Price on every row, then adds them up:
=SUMPRODUCT(D2:D9,E2:E9)| Order | Day | Product | Qty | Price | Unit cost | Report | Answer | |
|---|---|---|---|---|---|---|---|---|
| O101 | Mon | Earbuds | 2 | 35 | 20 | Revenue | ||
| O102 | Mon | Charger | 1 | 15 | 6 | Total cost | ||
| O103 | Tue | Phone case | 3 | 8 | 3 | Profit | ||
| O104 | Wed | Earbuds | 1 | 35 | 20 | Avg price per unit | ||
| O105 | Wed | Earbuds | 4 | 35 | 20 | Monday revenue | ||
| O106 | Thu | Charger | 2 | 15 | 6 | |||
| O107 | Fri | Phone case | 5 | 8 | 3 | |||
| O108 | Fri | Earbuds | 3 | 35 | 20 |
Key points
SUMPRODUCTMultiplies the ranges row by row, then adds up the results.
D2:D9,E2:E9The ranges to multiply, with a comma between them. They must be the same size.
Row by rowRow 2's Qty times row 2's Price, row 3's times row 3's, and so on, then one total.
Put together: =SUMPRODUCT(D2:D9,E2:E9). It gives the same answer as a Qty × Price column added up with SUM.
Practice
1. In I2, work out the total revenue: Qty times Price on every row, added up.
=SUMPRODUCT(D2:D9,E2:E9)2. In I3, work out the total cost: Qty times Unit cost on every row, added up.
=SUMPRODUCT(D2:D9,F2:F9)3. In I4, work out the profit: total revenue minus total cost. Use SUMPRODUCT for each.
=SUMPRODUCT(D2:D9,E2:E9)-SUMPRODUCT(D2:D9,F2:F9)4. In I5, work out the average price per unit sold: total revenue divided by the total Qty, rounded to 2 decimal places.
=ROUND(SUMPRODUCT(D2:D9,E2:E9)/SUM(D2:D9),2)5. In I6, work out Monday's revenue. Monday's orders are rows 2 and 3.
=SUMPRODUCT(D2:D3,E2:E3)Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.
Try this lesson free →