The Pick the tool guide
A list of single sales and four names: how much did each person sell? The task doesn't say how. Choosing the function, and making it survive a fill down, is the exercise.
Read the question first
"How much" in dollars is a total, so it's a sum. "Each rep" means only the rows with that name. A sum with a condition is SUMIF.
One way to write it
=SUMIF($B$2:$B$13, E2, $C$2:$C$13)The name in E2 stays unlocked so it moves down with the formula; the two ranges are locked so they don't. =SUMIFS($C$2:$C$13, $B$2:$B$13, E2) works too, with the sum range first. A pivot table gives the same totals without formulas.
Where it goes wrong
- COUNTIF. Counts the sales instead of adding them.
- AVERAGEIF. The average sale, not the total.
- No $. The ranges slide down as you fill, and the first sales drop out.
- $E$2. Every row looks up the first name.
The full lesson: SUMIF with a cell reference.
FAQ
- How do I total sales by person in Excel?
- List each name once, then use =SUMIF($B$2:$B$13,E2,$C$2:$C$13) next to the first name and fill down. A pivot table does the same.
- Why are my SUMIF totals too small after filling down?
- The ranges weren't locked, so they slid down with the formula. Lock them with $.
- SUMIF or SUMIFS?
- Either works with one condition. SUMIFS takes more conditions and puts the sum range first.
