Unique values that meet a condition
FILTER keeps only the customers on West rows and UNIQUE removes repeated names from that result.
=UNIQUE(FILTER(B2:B100,A2:A100="West"))The sheet: Column A is Region and column B is Customer. You want a list of each customer that bought in the West region, with no repeats.
How it works, step by step
- 01A2:A100="West" is TRUE for every West row.
- 02FILTER returns the Customer values from B2:B100 for those rows only.
- 03UNIQUE removes repeats so each customer appears once.
- 04The list spills downward, so cells below the formula must be empty.
The same logic in four tools
=UNIQUE(FILTER(B2:B100,A2:A100="West"))=UNIQUE(FILTER(B2:B100,A2:A100="West"))SELECT DISTINCT customer
FROM orders
WHERE region = 'West';customers = df.loc[df["region"] == "West", "customer"].drop_duplicates()Mistakes that break it
- !If no row matches, FILTER returns an error (#CALC! in Excel, #N/A in Sheets). In Excel add a third argument such as "None"; in Google Sheets wrap the formula in IFERROR instead.
- !Needs Microsoft 365 or Excel 2021 for Excel. Older versions cannot do this in one formula.
- !In Excel, blank cells in the filtered range show up as a 0 in the list.
Common questions
- How do I count the result?
- Wrap it in COUNTA, as in the count distinct with a condition entry.
- How do I sort the list?
- Wrap the whole thing in SORT.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.