BeginnerMath Functions
Total a month of sales with SUM
Add up a column of daily sales figures to get the monthly total.
SUM
3 min10 points
Excel for sales
Sales spreadsheets answer one question in many forms: who is ahead, who is behind, and what does that mean for their commission.
What the job actually asks for
Quota attainment, tiered commission, pipeline coverage and territory comparisons. The formulas are not complex, but the business rules are fiddly and the numbers are personal — if a commission calculation is wrong, someone notices immediately and it is awkward.
The recurring tasks
- Calculate attainment against quota by rep
- Apply tiered commission rules
- Rank territories by growth
- Flag deals slipping past close date
Where people get caught
Tiered logic tested in the wrong order. Nested conditions evaluate top down and stop at the first match, so checking the lowest threshold first quietly caps everyone at the bottom tier — and the sheet calculates cleanly while doing it.
What interviews test
Applying a tiered commission rule, calculating attainment against quota, and ranking reps without sorting the data.
Practice built from sales work
13 exercises using the situations above, ordered from most approachable to hardest.
- BeginnerStatistical Functions
Find the average order value with AVERAGE
Calculate the mean value of a set of customer orders.
AVERAGE
3 min10 points - BeginnerStatistical Functions
Find the largest and smallest order
Identify the extremes in a column of values.
MAX · MIN
4 min10 points - BeginnerFormulas
Calculate line totals with multiplication
Multiply quantity by unit price to get a line total.
3 min10 points - BeginnerFormulas
Calculate achievement against target
Divide actual sales by target to get an achievement percentage.
3 min10 points - BeginnerLogical Functions
Flag reps below target with IF
Use IF to label each rep as meeting or missing their target.
IF
5 min15 points - BeginnerMath Functions
Total sales for one region with SUMIF
Add up only the rows matching a text condition.
SUMIF
6 min15 points - IntermediateLookup & Reference
Look up a customer's region with XLOOKUP
Match a customer ID against a reference table to return their region.
XLOOKUP
8 min25 points - IntermediateLookup & Reference
Look up a unit price with VLOOKUP
Use VLOOKUP with exact match to pull prices from a product table.
VLOOKUP
8 min25 points - IntermediateMath Functions
Total sales for a region and month with SUMIFS
Apply two conditions at once to a conditional sum.
SUMIFS
9 min25 points - IntermediateLogical Functions
Band orders by size with nested IF
Return one of three labels depending on where a value falls.
IF
9 min25 points - IntermediateLogical Functions
Apply commission tiers with IFS
Return the right rate from several tiers without nesting.
IFS
10 min25 points - AdvancedData Analysis
Name the second-best rep with LARGE and INDEX
Combine ranking with a lookup to return a name, not just a number.
LARGE · INDEX · MATCH
14 min40 points