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.
Learning Objectives:
- Combine two conditions in SUMIFS, COUNTIFS and AVERAGEIFS
- See that all three apply the same AND test to decide which rows count
- Read range/criteria pairs as the way Excel expresses 'and' between conditions
Solve without hints for +5 XP
Interactive Spreadsheet
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 teaches (contains 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.