SUMIF with a separate sum_range
PremiumIn E2, use SUMIF to total the Amount of orders with more than 3 units.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Units | Amount | |||
| 2 | 1001 | 2 | 40 | |||
| 3 | 1002 | 5 | 110 | |||
| 4 | 1003 | 4 | 95 | |||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock SUMIF 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.
It comes up in this mock test: Excel assessment (Indeed format)
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
SUMIF checks the Units in B2:B4 and adds the Amount from C2:C4 on the rows where Units is more than 3.
Common mistake: Leaving out the third argument. SUMIF then adds up the units it checked, not the amounts.
The answer
- E2
=SUMIF(1B2:B4,2">3",3C2:C4)
In plain words: Add up 3the amounts in C2:C4 on the rows where 1the units in B2:B4 are 2more than 3.