Build a duplicate-free client list from an order log, and count who's on it
UNIQUE drops the repeats out of a column without needing it sorted first, and a spilled-range reference lets COUNTA count exactly as many rows as UNIQUE actually returned, rather than a fixed guess at how many that would be.
Task:
You handle order processing for Bramwell Wholesale Goods, a home-goods distributor. The order log below lists every sale this quarter, and a repeat client shows up once per order — several appear three or four times. Marketing wants a clean, duplicate-free client list for a seasonal mailing. In E2, use UNIQUE to list each client from B2:B9 once. In G2, count how many clients that list came to.
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 | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order ID | Client | Amount | Mailing List | Distinct Clients | ||
| 2 | SO-3001 | Meridian Builders | 2400 | ||||
| 3 | SO-3002 | Coastal Hardware | 890 | ||||
| 4 | SO-3003 | Meridian Builders | 1750 | ||||
| 5 | SO-3004 | Oakridge Supply | 640 | ||||
| 6 | SO-3005 | Coastal Hardware | 1200 | ||||
| 7 | SO-3006 | Vantage Construction | 3100 | ||||
| 8 | SO-3007 | Oakridge Supply | 980 | ||||
| 9 | SO-3008 | Meridian Builders | 560 |
What this exercise teachesMay contain the answer
UNIQUE(B2:B9) walks down the client column once and keeps only the first time each name appears, so Meridian Builders' second and third orders and Coastal Hardware's and Oakridge Supply's second orders never make it into the result — what's left is exactly one row per client, in the order each first ordered rather than alphabetized. That order is worth noticing: UNIQUE isn't sorting the list, just deduplicating it, so Meridian Builders leads the mailing list because it happens to be the first order in the log, not because of anything about the name itself. COUNTA(E2#) doesn't count B2:B9 or guess at a fixed number of rows — the '#' asks for whatever UNIQUE actually spilled, so if next quarter's log adds a fifth distinct client, E2# grows to match and COUNTA's answer grows with it, with nothing in either formula needing to change.