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
Fix a SUMIFS with its arguments swapped
The situation
A regional total in the monthly pack is returning zero and nobody can see why — the ranges look right, the region is spelled correctly, and the formula does not error. Someone has written it in the order SUMIF uses.
A1
fx
| Row number | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Region | Amount | West total | ||
| 2 | West | $1,200 | #VALUE! | ||
| 3 | East | $500 | |||
| 4 | South | $900 | |||
| 5 | West | $800 | |||
| 6 | North | $600 | |||
| 7 | South | $1,100 | |||
| 8 | East | $700 | |||
| 9 | West | $1,000 | |||
| 10 | South | $400 | |||
| 11 | North | $300 | |||
| 12 |
Functions in this exercise
- SUMIFS — Adds up values that meet several conditions at once.
Who this is for
Recommended next
Related exercises
- AdvancedLogical Functions
Fix a broken commission formula
Diagnose and correct a formula that returns the wrong answer.
IF
14 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 - AdvancedData Analysis
Fill a two-way summary with SUMIFS
Write one formula that stays correct filled both across and down a grid.
SUMIFS
14 min40 points