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.
Next with premium:AVERAGEIF and AVERAGEIFSSUMIFS
It comes up in this mock test: Excel assessment (Indeed format)
Need help?
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.
The cells to add. SUMIF checks the first range against the criteria and adds the cells in the same rows of sum_range.