SheetSense

Unique values that meet a condition

FILTER keeps only the customers on West rows and UNIQUE removes repeated names from that result.

fx
=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

  1. 01A2:A100="West" is TRUE for every West row.
  2. 02FILTER returns the Customer values from B2:B100 for those rows only.
  3. 03UNIQUE removes repeats so each customer appears once.
  4. 04The list spills downward, so cells below the formula must be empty.

The same logic in four tools

Excel
=UNIQUE(FILTER(B2:B100,A2:A100="West"))
Google Sheets
=UNIQUE(FILTER(B2:B100,A2:A100="West"))
SQL
SELECT DISTINCT customer
FROM orders
WHERE region = 'West';
Python (pandas)
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.