SheetSense

Count distinct values with a condition

FILTER keeps the West customers, UNIQUE removes repeats, and COUNTA counts what is left.

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

  1. 01FILTER(B2:B100,A2:A100="West") returns the Customer values on West rows.
  2. 02UNIQUE removes repeated customers from that list.
  3. 03COUNTA counts the remaining entries, which is the number of distinct customers.

The same logic in four tools

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