Find the range of one rep's closed-won deal sizes in a mixed pipeline log
MAXIFS and MINIFS are SUMIFS' cousins for extremes — restrict the same range with the same condition pairs, and get back the biggest or smallest match instead of a total.
Task:
You're prepping for a rep's performance check-in. The deal log below covers two reps across several pipeline stages, and you want to see how much Priya's closed-won deals actually range in size — her open deal and her lost deal shouldn't count, and neither should anything of Marcus's. In F2, use MAXIFS to find the largest Closed Won deal amount for Priya. In F3, use MINIFS to find the smallest.
Interactive Spreadsheet
Solve without hints for +5 XP
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Deal | Rep | Stage | Amount | ||
| 2 | D-101 | Priya | Closed Won | 18500 | Largest Closed Won (Priya) | |
| 3 | D-102 | Priya | Closed Lost | 9200 | Smallest Closed Won (Priya) | |
| 4 | D-103 | Marcus | Closed Won | 12300 | ||
| 5 | D-104 | Priya | Closed Won | 27600 | ||
| 6 | D-105 | Marcus | Closed Won | 8400 | ||
| 7 | D-106 | Priya | Open | 15000 | ||
| 8 | D-107 | Marcus | Closed Won | 31200 | ||
| 9 | D-108 | Priya | Closed Won | 6100 |
What this exercise teaches (contains the answer)
MAXIFS and MINIFS only look inside the rows where every condition passed to them holds, so Priya's Open deal and her Closed Lost deal drop out before either function compares anything, leaving just her three Closed Won amounts — 18500, 27600 and 6100 — with 27600 the largest and 6100 the smallest. Marcus's Closed Won deals never enter the comparison either, because the Rep condition alone rules his rows out regardless of stage. F2 and F3 share the exact same two conditions; only the function picking off the extreme changes.