FILTER, UNIQUE and SORT: an Excel interview question

Nest SORT, UNIQUE and FILTER, inside out: =SORT(UNIQUE(FILTER(A2:A9,B2:B9=F1))). FILTER keeps the customers whose region is the one in F1, UNIQUE lists each of them once, and SORT puts them A to Z. The result spills down as many cells as it needs and updates the moment an order or the region in F1 changes. Wrap it in ROWS to count the customers instead of listing them.

The question

“Here’s this quarter’s order list. On a summary sheet, list the customers who ordered in whichever region I type in a cell, each one once, alphabetically. It should update by itself when new orders come in.”

The older version of this question expects a pivot table or Remove Duplicates. If the company runs Microsoft 365, the interviewer usually wants to see whether you know there’s now a one-formula answer.

What they’re really testing

• Whether you know dynamic arrays. One formula that returns a whole list, instead of a manual copy, a pivot table or a helper column.
• The order of the steps. Filter first, then remove repeats, then sort.
• Spill behaviour. What happens when the cells below aren’t empty, or when nothing matches.
• Counting a list. A follow-up such as “and how many are there?” checks you can wrap the result.

The worked answer, one step at a time

The orders are in A1:C9 and the region to show in F1. Say each step out loud as you add it. First, FILTER keeps the names of the West orders, repeats and all:

E3fx=FILTER(A2:A9,B2:B9=F1)

Row	A	B	C	D	E	F
1	Customer	Region	Amount		Region	West
2	Rowan Ltd	West	820
3	Atlas Co	East	450		Rowan Ltd
4	Mira Foods	West	1300		Mira Foods
5	Rowan Ltd	West	640		Rowan Ltd
6	Kite Labs	North	980		Atlas Co
7	Atlas Co	West	300		Mira Foods
8	Mira Foods	West	210
9	Orbit Inc	East	760

UNIQUE around it lists each customer once, in the order they first appear:

SORT around that puts them A to Z. That’s the whole answer:

E3fx=SORT(UNIQUE(FILTER(A2:A9,B2:B9=F1)))

Row	A	B	C	D	E	F
1	Customer	Region	Amount		Region	West
2	Rowan Ltd	West	820
3	Atlas Co	East	450		Atlas Co
4	Mira Foods	West	1300		Mira Foods
5	Rowan Ltd	West	640		Rowan Ltd
6	Kite Labs	North	980
7	Atlas Co	West	300
8	Mira Foods	West	210
9	Orbit Inc	East	760

Type another region in F1 and the list changes size to fit. To pick up new orders as well, turn the data into a table (Ctrl+T) and point the formula at its columns, such as Orders[Customer]: a table’s columns grow as rows are added.

The follow-up: how many customers?

Wrap the list in ROWS, or COUNTA, and you get a count instead of a list:

The West region has five orders from three customers. A COUNTIF on the region would say five, which answers a different question.

When nothing matches

With a region that has no orders, FILTER has nothing to return, and without a fallback it shows #CALC!. Its third argument is what to show instead:

The count needs the same care. COUNTA counts an error as a value, so for a region with no orders it says 1 customer, with or without the fallback. Count with ROWS and catch the #CALC! with IFERROR, and an empty region shows 0:

The order of the steps matters

Remove the repeats first and then try to filter, and FILTER gets a list of names that no longer lines up with the regions beside it. The ranges are different sizes, so it returns #VALUE!:

Filter while the names still sit next to their regions, then take UNIQUE of what’s left.

Common wrong answers, and why they fail

1. Counting orders instead of customers. COUNTIF on the region counts every order, repeats included.
2. UNIQUE before FILTER. The ranges no longer line up, and FILTER returns #VALUE!.
3. Something in the way. A note or a value below the formula blocks the list, which shows #SPILL! until the cells are cleared.
4. No fallback for an empty result. A region with no orders shows #CALC!.
5. Copying the formula down. A spilling formula goes in one cell only. Copied down, each copy sits in the way of the list above it, so the formulas above show #SPILL!.

Practise it: count the different customers

A different order list and a count instead of a list. We check your count, then switch the region and change a customer’s name.

Your task: In E2, count how many different customers ordered in the region in E1.

Graded live

E2fx

Row	A	B	C	D	E
1	Customer	Region		Region	East
2	Lumen AG	East		Customers
3	Bay Traders	West
4	Lumen AG	East
5	Northwind	East
6	Peak Supply	North
7	Bay Traders	East
8	Northwind	East
9	Cedar Ltd	West
10	Lumen AG	East

Write the formula in the bar above, then press Check or Enter.

Hint

FILTER(A2:A10,B2:B10=E1) keeps the region's names, UNIQUE lists each once, and ROWS (or COUNTA) counts them.

Solution

=ROWS(UNIQUE(FILTER(A2:A10,B2:B10=E1))) Any formula that returns the same result passes.

Check

How to answer it in the interview

“FILTER to the region in F1, UNIQUE so each customer shows once, SORT for A to Z, all in one cell, and it spills. I’d give FILTER a fallback for a region with no orders, and keep the cells below it clear.” If they ask for an older version, say pivot table or Remove Duplicates, and that neither updates by itself. More questions like this one: Excel interview questions.