Count Distinct Customers with UNIQUE and FILTER
Excel version: UNIQUE and FILTER need Excel 2021 or Microsoft 365.
Your task: In E2:E4, count distinct customers, distinct West customers, and the average orders per customer.
Full instructions, scenario and goal
Scenario
A customer-success manager has a list of recent orders. Many customers appear more than once. She wants to know how many different customers placed orders, how many of them are in the West region, and how many orders a customer places on average.
Your task
- In E2, count how many different customers appear in column B.
- In E3, count how many different customers placed orders in the West region.
- In E4, calculate the average number of orders per customer: all orders divided by the distinct customers.
- Each answer must be one number, not a list. Select Check Answer when you are done.
You are done when
- Each customer is counted once.
- E3 only counts West orders.
- Every answer is a single value, not a spilled list.
What you will practise
Remove duplicates with UNIQUE, filter a list with FILTER, and count the result with ROWS to get a single number.
The idea in 30 seconds
UNIQUE(range) returns the list of values with the duplicates removed, and FILTER(range, condition) returns only the rows that meet a condition. They are dynamic array functions: a result with several values spills into the cells below. On their own they produce a list; to get a single number out of a list, wrap it in a function that summarises it. ROWS(list) returns how many items the list has, so ROWS(UNIQUE(range)) is a distinct count.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell E2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
UNIQUE(range) returns the list of values with the duplicates removed, and FILTER(range, condition) returns only the rows that meet a condition. They are dynamic array functions: a result with several values spills into the cells below. On their own they produce a list; to get a single number out of a list, wrap it in a function that summarises it. ROWS(list) returns how many items the list has, so ROWS(UNIQUE(range)) is a distinct count.
Functions can be nested, working from the inside out. FILTER first narrows a list to one region, UNIQUE then removes the repeats among those rows, and ROWS counts what is left. Writing the result in a cell without the wrapper would spill a list into the cells below it, which is not what a report cell should contain. Distinct counts answer questions that a plain COUNT cannot: how many different customers, not how many orders.
Skills you will practise
- UNIQUE
- FILTER
- ROWS
- Dynamic arrays
- Distinct count
Complete first: Count Support Tickets with COUNTIF and COUNTIFS
Common mistakes to avoid
- Counting orders instead of customers
- COUNTA(B2:B13) counts every row, including repeat orders from the same customer.
- Leaving a list in the cell
- UNIQUE or FILTER alone spill several values into the cells below. Wrap the list in ROWS to get one number.