Count distinct emails, customers, or SKUs without pivot tables. Describe your data and get the right unique-count formula for your Excel version.
Files are processed locally and never uploaded.
Describe what you're counting in the question box above — edit the example to match your column and data.
Submit and get a working unique-count formula matched to your Excel version, with an explanation.
Copy it into your sheet, point it at your range, and get your distinct count.
If you have Microsoft 365 or Excel 2021, counting unique values is a two-function job: =COUNTA(UNIQUE(A2:A1000)). UNIQUE spills the distinct values into a temporary array, COUNTA counts the non-blank entries. It's readable, fast, and handles the common case — unique emails, unique customers, unique order IDs — in one line.
Note the range choice: A2:A1000 excludes the header row, because UNIQUE would otherwise count "Email Address" as one of your unique values. And COUNTA (not COUNT) is deliberate — it counts text values like email addresses, where COUNT would return zero.
Without dynamic arrays, the classic formula is =SUMPRODUCT(1/COUNTIF(A2:A1000, A2:A1000)). It works by asking, for each value, "how many times do you appear?" and adding up the reciprocals — so a value appearing 3 times contributes ⅓ + ⅓ + ⅓ = 1. Clever, correct, and completely unreadable to anyone who didn't write it.
Two warnings: it's slow on large ranges (tens of thousands of rows will lag), and blank cells break it — COUNTIF counts blanks too, producing division-by-zero errors. The generator produces the blank-safe variant when your description mentions empty cells, and tells you honestly when your data is big enough to prefer a pivot table instead.
Here's the subtlety that ruins counts: most "unique" formulas count a blank cell as a value. If your column has 50 emails and 200 blanks, =COUNTA(UNIQUE(A2:A1000)) returns 51, not 50. Whether that's wrong depends on what you're measuring — but it's wrong often enough to mention.
The fix is filtering blanks first: =COUNTA(UNIQUE(FILTER(A2:A1000, A2:A1000<>""))). Mention empty cells when you describe your data and the generator includes the FILTER wrapper automatically. This is exactly the kind of detail people forget to Google and then debug for an hour.
Real questions are rarely "how many unique emails" — they're "how many unique customers bought in Q1" or "how many distinct products did the West region order." In modern Excel that's =COUNTA(UNIQUE(FILTER(A2:A1000, B2:B1000="Q1"))) — filter first, then unique, then count. The pattern composes cleanly.
On older Excel this gets painful fast (array SUMPRODUCT with multiple conditions), which is where a pivot table genuinely wins: drag the field to Rows, and the row count is your distinct count. The generator will tell you when your question has outgrown a single formula — that's not a failure, it's the right tool for the job.
Excel's built-in Remove Duplicates (Data tab) does count uniques — sort of. It destructively deletes rows, which is fine for a one-off cleanup but useless for a living report that needs to recalculate when data changes. A formula keeps counting as new rows arrive; Remove Duplicates is a snapshot.
The middle ground people forget: a pivot table with "Distinct Count" (available when data is added to the Data Model) gives you distinct counts that refresh with one click. For dashboards, that's often better than any formula. For a single cell in a report, the formula wins.
On Microsoft 365 or Excel 2021: =COUNTA(UNIQUE(A2:A1000)). On Excel 2019 or older: =SUMPRODUCT(1/COUNTIF(A2:A1000,A2:A1000)). Describe your data to the generator and it picks the right one for your version, including blank-cell handling.
UNIQUE treats blanks as a value, so COUNTA counts them. Wrap the range in FILTER to exclude blanks: =COUNTA(UNIQUE(FILTER(A2:A1000,A2:A1000<>""))). Mention empty cells in your description and the generator adds this automatically.
Yes — filter first, then count uniques: =COUNTA(UNIQUE(FILTER(emails, regions="West"))). For older Excel without FILTER, a pivot table with Distinct Count is usually the saner path; the generator will say so when it applies.
Not directly — COUNTIF counts occurrences of one specific value. The SUMPRODUCT(1/COUNTIF(...)) trick inverts those occurrence counts to derive a unique count. It's the standard pre-2021 approach, but UNIQUE/COUNTA replaces it wherever dynamic arrays are available.