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.
Next with premium:VLOOKUPINDEX and MATCH
Need help?
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)
Multiply a test into the product, for example =SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7). Rows where the test is FALSE count as 0.
--(A2:A7="North") turns TRUE and FALSE into 1 and 0, so the test can be passed as its own argument.