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
Fill a two-way summary with SUMIFS
The situation
Harbor Supply's monthly pack has a region-by-month grid that someone rebuilds by hand every month. Done properly it is one formula, written once in the top-left cell and dragged across and down — but only if the anchoring is exactly right.
A1
fx
| Row number | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Region | Month | Amount | Oct | Nov | ||
| 2 | West | Oct | $1,200 | West | |||
| 3 | West | Oct | $800 | East | |||
| 4 | East | Oct | $500 | South | |||
| 5 | South | Oct | $900 | ||||
| 6 | West | Nov | $1,000 | ||||
| 7 | East | Nov | $700 | ||||
| 8 | South | Nov | $1,100 | ||||
| 9 | West | Nov | $600 | ||||
| 10 | East | Oct | $400 | ||||
| 11 | South | Oct | $300 | ||||
| 12 | West | Oct | $200 | ||||
| 13 | East | Nov | $250 | ||||
| 14 |
Functions in this exercise
- SUMIFS — Adds up values that meet several conditions at once.
Who this is for
Recommended next
Related exercises
- AdvancedData Analysis
Build a regional summary from raw orders
Produce a summary line combining count, total and average for one region.
COUNTIF · SUMIF
16 min40 points - AdvancedLookup & Reference
Lock ranges so a lookup survives fill-down
Use absolute references so a lookup does not break when copied down a column.
XLOOKUP
12 min40 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