UNIQUE, then a count beside each value
Spill the distinct list, then count each item against the source.
Task:
You want a quick summary of tickets by category. In E2 list each category once with UNIQUE, then in F2:F4 count how many tickets each one has.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of UNIQUE
0/4The same formula in the other shapes it takes at work.
- Pull the distinct regions out of an export
UNIQUE returns each value once, in the order it first appears.
CoreBeginner - UNIQUE, then a count beside each value
Spill the distinct list, then count each item against the source.
Intermediate - UNIQUE with exactly_once: the one-time customers
UNIQUE's third argument keeps only values that appear a single time.
Intermediate - UNIQUE over two columns: distinct pairs
Give UNIQUE several columns and it removes duplicate rows, not duplicate values.
Intermediate
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 | F | |
|---|---|---|---|---|---|---|
| 1 | Ticket | Category | Category | Tickets | ||
| 2 | T1 | Billing | ||||
| 3 | T2 | Login | ||||
| 4 | T3 | Billing | ||||
| 5 | T4 | Delivery | ||||
| 6 | T5 | Login | ||||
| 7 | T6 | Billing | ||||
| 8 | T7 | Delivery | ||||
| 9 | T8 | Billing |
What this exercise teachesMay contain the answer
UNIQUE builds the left-hand column of a summary table for you, in the order categories first appear. Counting against each spilled value turns it into a frequency table without a pivot — and when a new category appears in the data, it appears in the list too.