Revenue for one region with SUMPRODUCT
PremiumIn F1, work out the revenue (units × price) for the North rows only, in one formula. Multiply a test on the region by the two columns inside SUMPRODUCT: SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Units | Price | North revenue | |||
| 2 | North | 12 | 25 | ||||
| 3 | South | 8 | 30 | ||||
| 4 | North | 5 | 40 | ||||
| 5 | West | 10 | 25 | ||||
| 6 | North | 3 | 60 | ||||
| 7 | South | 6 | 40 | ||||
| 8 |
Premium lesson
Unlock SUMPRODUCT and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
Why this works
(A2:A7="North") gives TRUE or FALSE for each row. Multiplied by a number, TRUE counts as 1 and FALSE as 0, so every row from another region becomes 0. What is left is 12×25 + 5×40 + 3×60 = 300 + 200 + 180 = 680.
Common mistake: Passing the test as its own argument without multiplying it. SUMPRODUCT then reads TRUE and FALSE as 0 and the total comes out 0.
The answer
- F1
=SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7)