Count distinct values with a condition
FILTER keeps the West customers, UNIQUE removes repeats, and COUNTA counts what is left.
=COUNTA(UNIQUE(FILTER(B2:B100,A2:A100="West")))The sheet: Column A is Region and column B is Customer. You want to know how many different customers bought in the West.
How it works, step by step
- 01FILTER(B2:B100,A2:A100="West") returns the Customer values on West rows.
- 02UNIQUE removes repeated customers from that list.
- 03COUNTA counts the remaining entries, which is the number of distinct customers.
The same logic in four tools
=COUNTA(UNIQUE(FILTER(B2:B100,A2:A100="West")))=COUNTA(UNIQUE(FILTER(B2:B100,A2:A100="West")))SELECT COUNT(DISTINCT customer)
FROM orders
WHERE region = 'West';n = df.loc[df["region"] == "West", "customer"].nunique()Mistakes that break it
- !If no rows match, FILTER errors and so does the count. Wrap it in IFERROR(...,0).
- !In Excel, blank customer cells come through FILTER as 0 and are counted as one entry, while SQL COUNT(DISTINCT) and pandas nunique skip blanks.
- !Spelling and spacing differences count as different customers.
Common questions
- Is there an older-Excel version?
- Yes, but it is long: SUMPRODUCT((A2:A100="West")/COUNTIFS(A2:A100,A2:A100,B2:B100,B2:B100)), which fails on blank rows.
- How do I count distinct values with no condition?
- See the count distinct values entry.
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.