Count tickets on more than one condition at once
COUNTIF answers one question about one column. A real queue asks about two or three columns together.
Task:
You run the support queue for a small SaaS product. Nine tickets came in today, each logged with its agent, priority and current status. In B11 count how many High-priority tickets are still open across the whole queue. In B12 count how many High-priority tickets Jordan personally has already resolved.
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 | |
|---|---|---|---|
| 1 | Agent | Priority | Status |
| 2 | Jordan | High | Resolved |
| 3 | Priya | Low | Resolved |
| 4 | Jordan | High | Open |
| 5 | Priya | High | Resolved |
| 6 | Jordan | Medium | Resolved |
| 7 | Priya | High | Open |
| 8 | Jordan | High | Resolved |
| 9 | Priya | Medium | Resolved |
| 10 | Jordan | High | Open |
| 11 | High-priority tickets still open | ||
| 12 | High-priority tickets Jordan has resolved |
What this exercise teachesMay contain the answer
COUNTIFS only counts a row once every condition passed to it is true for that row, which is why each range has to run over the same rows as the others — line up B2:B10 against a differently-shifted span of C2:C10 and the formula stops describing one ticket and starts comparing rows that have nothing to do with each other. B12 answers a narrower question than B11 without any new mechanism: adding Jordan as a third range/condition pair narrows the same single pass rather than needing a separate step that first finds Jordan's tickets and then filters those. That is the difference between COUNTIFS and stacking COUNTIF calls — one evaluates every condition together, so going from two conditions to three costs one more pair, not a rewrite.