Statistical FunctionsIntermediate

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

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

9 rows × 6 columns2 cells you fill in
ABCDEF
1DealRepStageAmount
2D-101PriyaClosed Won18500Largest Closed Won (Priya)
3D-102PriyaClosed Lost9200Smallest Closed Won (Priya)
4D-103MarcusClosed Won12300
5D-104PriyaClosed Won27600
6D-105MarcusClosed Won8400
7D-106PriyaOpen15000
8D-107MarcusClosed Won31200
9D-108PriyaClosed Won6100
What this exercise teachesMay contain 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.