Count Distinct Values Meeting a Condition

Excel Formulas › Count

All versionsSUMPRODUCT

How many different customers ordered in the East region? Count distinct values that also meet a condition — a SUMPRODUCT reciprocal trick does it without a pivot.

Quick formula: distinct names where region = East:
=SUMPRODUCT((region="East")/COUNTIFS(name, name, region, "East", region, "East"))
Simpler robust form: =SUMPRODUCT((region="East")*(MATCH(name&region, name&region, 0)=ROW(name)-ROW(firstcell)+1)).

Functions used (tap for the full reference guide):

SUMPRODUCTCOUNTIFS

The example

Distinct customers in the East region.

AB
1RegionCustomer
2EastAnn
3EastAnn
4EastBo
5Distinct East2

The formula

Distinct count with a filter:

=SUMPRODUCT((region="East")/COUNTIFS(name,name,region,"East")) // Ann, Bo = 2

How it works

The reciprocal trick, narrowed by a condition:

  1. Each matching row contributes 1 / (count of that name within East).
  2. A name appearing 3 times adds 1/3 three times = 1; so each distinct name counts once.
  3. The (region="East") factor zeroes out rows that don’t meet the condition.
  4. SUMPRODUCT totals the contributions — the count of distinct names in East.

Excel 365 is far simpler: =COUNTA(UNIQUE(FILTER(name, region="East"))) — filter to the region, take the uniques, count them. The SUMPRODUCT version is for older Excel.

Try it: interactive demo

Live demo

Lines “region,name”; count distinct in East.

Distinct East:

Variations

365 version

Filter, unique, count:

=COUNTA(UNIQUE(FILTER(name, region="East")))

Distinct overall

No condition:

=SUMPRODUCT(1/COUNTIF(name, name))

Two conditions

Add another factor in SUMPRODUCT.

Pitfalls & errors

No blanks in the range. The reciprocal trick divides by COUNTIF, so an empty cell causes #DIV/0!. Filter blanks out.

Match the criteria in COUNTIFS. The denominator must count within the same condition, or the math is off.

365 is easier. UNIQUE+FILTER avoids the fragile reciprocal entirely.

Practice workbook

📊
Download the free Count Distinct Values Meeting a Condition practice workbook
A distinct-with-criteria sheet with the 365, overall, and two-condition variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count distinct values with a condition in Excel?
In 365: =COUNTA(UNIQUE(FILTER(name, region="East"))). In older Excel, use the SUMPRODUCT reciprocal: =SUMPRODUCT((region="East")/COUNTIFS(name,name,region,"East")).
Why does the SUMPRODUCT version error?
It divides by COUNTIF, so blank cells cause #DIV/0!. Remove blanks from the range first.
How do I count distinct values overall?
Use =SUMPRODUCT(1/COUNTIF(range, range)) or =COUNTA(UNIQUE(range)) in 365.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Distinct count by group · Count unique values · Unique with count

Function references: SUMPRODUCT · COUNTIFS