Distinct Count by Group

Excel Formulas › Count

Excel 365Legacy alt

“How many different products did each region sell?” is a distinct count per group — trickier than a plain distinct count. Excel 365 does it with UNIQUE + FILTER; any version can with a SUMPRODUCT ratio trick.

Quick formula: to count distinct products (B) for the region in E2 (Excel 365):
=COUNTA(UNIQUE(FILTER(B2:B100, A2:A100=E2)))
FILTER keeps one region’s products, UNIQUE de-duplicates them, and COUNTA counts what’s left.

Functions used (tap for the full reference guide):

UNIQUEFILTERSUMPRODUCT

The example

Orders by region and product. How many distinct products did West sell?

AB
1RegionProduct
2WestWidget
3EastWidget
4WestGadget
5WestWidget
6West distinct products:2

The formula

Distinct products for West (Widget, Gadget — Widget appears twice but counts once):

=COUNTA(UNIQUE(FILTER(B2:B5, A2:A5="West"))) // {Widget, Gadget} → 2

How it works

Filter to the group first, then de-duplicate:

  1. FILTER(B2:B5, A2:A5="West") returns just West’s products: {Widget, Gadget, Widget}.
  2. UNIQUE(…) collapses that to the distinct set: {Widget, Gadget}.
  3. COUNTA(…) counts them — 2.
  4. Repeat for each group, or build a full per-group table by feeding it each region label.

One formula for the whole table (365): =GROUPBY(A2:A100, B2:B100, COUNTA, , , , , TRUE) patterns vary — but a spilled UNIQUE region list beside per-region distinct counts gives a tidy summary.

Try it: interactive demo

Live demo

Pick a region; see how many distinct products it has.

Distinct products:

Variations

Legacy SUMPRODUCT ratio (any version)

Counts distinct items within a group without UNIQUE:

=SUMPRODUCT((A2:A100=E2)/COUNTIFS(A2:A100, A2:A100, B2:B100, B2:B100))

Distinct count of the whole column

No grouping:

=COUNTA(UNIQUE(B2:B100))

Build a per-group table

Spill the region list, then count beside each:

=COUNTA(UNIQUE(FILTER(B:B, A:A=D2)))

Pitfalls & errors

Blanks inflate the count. An empty product cell becomes a “distinct” value. Filter them out: FILTER(B2:B5, (A2:A5="West")*(B2:B5<>"")).

The SUMPRODUCT ratio needs matching ranges and breaks on blanks (divide-by-zero). Guard blanks or use the 365 method.

UNIQUE/FILTER need Excel 365/2021. Older versions show #NAME? — use the SUMPRODUCT version or a PivotTable with “Distinct Count.”

Practice workbook

📊
Download the free Distinct Count by Group practice workbook
Orders with the FILTER+UNIQUE distinct-by-group count (result shown), the legacy SUMPRODUCT ratio, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count distinct values by group in Excel?
In Excel 365 use =COUNTA(UNIQUE(FILTER(items, group=g))). FILTER keeps one group's items, UNIQUE de-duplicates, and COUNTA counts. A PivotTable with Distinct Count works too.
How do I do a distinct count by group without UNIQUE?
Use a SUMPRODUCT ratio: =SUMPRODUCT((group=g)/COUNTIFS(group, group, item, item)), which sums 1/occurrence so each distinct item counts once. Guard against blanks.
How do I count distinct values for the whole column?
Use =COUNTA(UNIQUE(range)) in Excel 365, or =SUMPRODUCT(1/COUNTIF(range, range)) in older versions.

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: Count unique values · Two-way summary table · Unique sorted list

Function references: UNIQUE · FILTER · SUMPRODUCT