Build a sorted, duplicate-free category breakdown from a ticket log with UNIQUE, SORT and COUNTIF
UNIQUE strips a log down to the categories that actually appear; SORT nested around it puts them in alphabetical order instead of log order; and COUNTIF, pointed straight at the cells that result lands in, counts each one without a category name ever being typed twice.
Task:
You triage tickets for an internal IT helpdesk, and the raw export lists one row per ticket with no summary of what's actually coming in. In D2, use SORT and UNIQUE together to pull the distinct categories out of B2:B11 in alphabetical order — one formula, typed once, that fills D2 down through D5 on its own. In E2:E5, use COUNTIF to count how many tickets belong to each of those categories, pointing it at the category already sitting in column D rather than typing the name again.
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 | E | |
|---|---|---|---|---|---|
| 1 | Ticket ID | Category | Category | Tickets | |
| 2 | TKT-5001 | Network | |||
| 3 | TKT-5002 | Hardware | |||
| 4 | TKT-5003 | Software | |||
| 5 | TKT-5004 | Network | |||
| 6 | TKT-5005 | Access | |||
| 7 | TKT-5006 | Software | |||
| 8 | TKT-5007 | Network | |||
| 9 | TKT-5008 | Hardware | |||
| 10 | TKT-5009 | Software | |||
| 11 | TKT-5010 | Access |
What this exercise teachesMay contain the answer
UNIQUE(B2:B11) collapses ten ticket rows down to the four categories that actually show up — Access, Hardware, Network and Software — without caring that Network appears three times before Access ever does. Wrapped in SORT, that short list lands in D2 already in alphabetical order rather than log order, and because both functions spill, typing the pair once in D2 fills D2:D5 on its own; nothing needs copying down. COUNTIF then runs back over the same ten rows in column B for each of those four results in turn, but it's pointed at D2, D3, D4 and D5 rather than retyped text — the identical values SORT just placed there, not a second copy of them that a typo could pull out of step with what SORT actually wrote. That's why Network ends up with 3 next to it and Access with 2, in a table that never once repeats a category name by hand.