Total closed deals by region with SUMIFS
One number the sales manager actually asked for, buried in a log that answers a different question by default.
Task:
The regional manager wants to know how the West region's closed deals are doing. From the deal log, work out the total value of closed deals in the West region in B8, how many closed West deals there were in B9, and the average size of those deals in B10.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve 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.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Region | Status | Amount |
| 2 | Alicia Ferrer | West | Closed | 4200 |
| 3 | Ben Okoro | East | Closed | 3100 |
| 4 | Priya Nair | West | Open | 2600 |
| 5 | Diego Vela | West | Closed | 5100 |
| 6 | Hana Kobayashi | East | Open | 1900 |
| 7 | ||||
| 8 | Total closed value (West) | |||
| 9 | Number of closed deals (West) | |||
| 10 | Average closed deal size (West) |
What this exercise teachesMay contain the answer
SUMIFS narrows the range with an AND across every criteria pair before it adds anything up, which is why Ben's closed East deal and Priya's still-open West deal both drop out even though each one satisfies exactly one of the two conditions on its own. COUNTIFS applies that identical AND test to count rows instead of summing them, and AVERAGEIFS applies it again to average them — three separate formulas, but one shared definition of which rows qualify, which is why dividing the total by the count reproduces the average without either formula referring to the other.