SUMIF with the criterion in a cell
PremiumIn E2, total the Sales of the rep named in D2. Point SUMIF at D2 instead of typing the name, so the total follows when D2 changes.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Sales | Rep | Total | ||
| 2 | Anna | 1200 | Ben | |||
| 3 | Ben | 800 | ||||
| 4 | Anna | 950 | ||||
| 5 | Carl | 1100 | ||||
| 6 | Ben | 400 | ||||
| 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:SUMIFSCOUNTIFS
Need help?
Why this works
With D2 as the condition, the formula reads the name from the cell. Type another rep's name in D2 and the total changes without touching the formula.
Common mistake: Putting the cell in quotes, like "D2". SUMIF then looks for the text D2 and finds nothing.
The answer
- E2
=SUMIF(1A2:A6,2D2,3B2:B6)
In plain words: Add up 3the sales in B2:B6 on the rows where 1the rep in A2:A6 is 2the name in D2.