📎 This article includes 1 downloadable practice file ↓
“How many customers bought from us in the North region?” COUNTIF can tell you how many orders came from North, but not how many different customers placed them. Counting distinct values — with or without a condition — needs a combination.
In this article
Excel 365 / 2021: UNIQUE + FILTER
=ROWS(UNIQUE(D2:D201)) ' distinct customers overall
=ROWS(UNIQUE(FILTER(D2:D201, B2:B201="North"))) ' distinct customers in North
=ROWS(UNIQUE(FILTER(E2:E201, (C2:C201="Asha")*(L2:L201="Paid")))) ' distinct products in Asha's paid ordersFILTER keeps the matching rows, UNIQUE removes repeats, ROWS counts what’s left. COUNTA works too instead of ROWS.
=IFERROR(ROWS(UNIQUE(FILTER(…))), 0), or give FILTER a third argument and subtract accordingly.Older Excel: SUMPRODUCT + COUNTIF
=SUMPRODUCT(1 / COUNTIF(D2:D201, D2:D201))Each customer appearing 4 times contributes 1/4 four times — adding up to exactly 1. Sum over everyone and you get the number of distinct customers.
With a condition
=SUMPRODUCT((B2:B201="North") / COUNTIFS(D2:D201, D2:D201, B2:B201, B2:B201))The COUNTIFS counts each customer within its own region, and the condition in the numerator zeroes out other regions.
Blank cells break the classic version with #DIV/0!. Guard them:
=SUMPRODUCT((D2:D201<>"") / COUNTIF(D2:D201, D2:D201 & ""))Distinct count in a pivot table
When you insert the pivot, tick Add this data to the Data Model. Then in the value field settings choose Distinct Count. Easiest option for a report with many breakdowns.
Distinct count per group (a summary table)
=LET(r, UNIQUE(B2:B201), HSTACK(r, MAP(r, LAMBDA(x, ROWS(UNIQUE(FILTER(D2:D201, B2:B201=x)))))))One formula that lists every region with its number of distinct customers.
Where people go wrong
| Symptom | Cause |
|---|---|
| Count too high | “Raj Traders” and “Raj Traders ” (trailing space) count as two customers. UNIQUE ignores upper/lower case, but not spaces — clean with TRIM first |
| #DIV/0! in SUMPRODUCT version | Blank cells in the range |
| Very slow workbook | SUMPRODUCT/COUNTIF over whole columns — limit to the real range or use a Table |
| Numbers and text counted separately | 101 (number) vs “101” (text) |
Practice
The combo practice workbook below has a Sales sheet of 200 orders and a task for every formula on this page. Type your formula in the yellow column; the check turns green when the answer matches. The Answers sheet has working versions.
More combinations: all formula combos · functions used here are explained in the Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.