Average response time for one priority, one team
COUNTIFS and AVERAGEIFS both take range/criteria pairs — stack two and only rows matching all of them count.
Task:
You run the IT help desk queue for a mid-size company. This week's tickets are logged with their priority, the team that handled them, and how many hours each took to resolve. Leadership wants to know how the Network team is doing specifically on High-priority work: how many such tickets came through, and how long they took to resolve on average. Put the count in D10 and the average resolution time, in hours, in D11.
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 | Ticket | Priority | Team | Hours |
| 2 | TCK-101 | High | Network | 4 |
| 3 | TCK-102 | Low | Helpdesk | 1.2 |
| 4 | TCK-103 | High | Network | 6 |
| 5 | TCK-104 | Medium | Security | 3.5 |
| 6 | TCK-105 | High | Helpdesk | 2 |
| 7 | TCK-106 | High | Network | 5 |
| 8 | TCK-107 | Low | Network | 0.8 |
| 9 | TCK-108 | Medium | Network | 2.5 |
| 10 | High-priority Network tickets | |||
| 11 | Average resolution hours (High/Network) |
What this exercise teachesMay contain the answer
COUNTIF and AVERAGEIF only ever test one condition, so isolating "High priority and Network" needs their S-suffixed siblings, which take as many range/criteria pairs as required and only count a row where every pair matches. TCK-105 is High but handled by Helpdesk, so the second pair drops it from both the count and the average; TCK-107 and TCK-108 are Network but not High, so the first pair drops them. What is left — TCK-101, TCK-103 and TCK-106 — is the three rows behind D10, and AVERAGEIFS only feeds D11 the Hours from those same three rows rather than the whole column, which is why their average of 4, 6 and 5 comes out to a clean 5.