Count Unique Values in Excel
Count how many distinct values a range contains, using UNIQUE in modern Excel or a SUMPRODUCT formula in older versions.
UNIQUE
Count how many distinct values a range contains, using UNIQUE in modern Excel or a SUMPRODUCT formula in older versions.
Column of values to de-duplicate. Trim it to the rows you actually use - blank cells add an extra entry.
=COUNTA(UNIQUE(A2:A100))Worked Example
A2:A11 lists 10 order IDs for the day, three of which repeat an ID already in the list.
=COUNTA(UNIQUE(A2:A11))Returns: 7 - the number of distinct order IDs among the 10 rows.
Checks Before You Paste
- •No UNIQUE function in your Excel? On Excel 2019 and earlier the classic replacement is =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)), which gives the same distinct count. It returns #DIV/0! if any cell in the range is blank, so the blank-safe form is =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")).
- •In Excel, blank cells inside the range make UNIQUE emit a 0, so the count comes back one higher than expected. Either size the range to the filled rows or use =COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>""))), which drops the blanks before counting and works the same way in Google Sheets.
- •The legacy SUMPRODUCT version compares every cell against every other cell, so never point it at a whole column like A:A - a million rows will lock the sheet up. UNIQUE is far cheaper, which is another reason to move the formula over when the file allows it.
How UNIQUE works
Availability: UNIQUE requires Excel 2021, Excel for Microsoft 365, or Excel for the web; it is also in Google Sheets, which additionally has COUNTUNIQUE for the count in one step. In Excel 2019 and earlier UNIQUE does not exist and the formula returns #NAME?, so those versions need the SUMPRODUCT approach below, a PivotTable with Distinct Count, or Power Query's Remove Duplicates.
Counting distinct values means asking how many different things are in a list, rather than how many entries it contains. A column of 500 orders placed by 128 customers has 500 rows and 128 distinct customer names. Excel has no single DISTINCTCOUNT function, so the count is built by combining two functions: UNIQUE reduces the list to one row per distinct value, and COUNTA counts what comes back.
UNIQUE returns a spilled array, which is why the pattern works. =UNIQUE(A2:A100) writes each distinct value into its own cell down the sheet, and wrapping it in COUNTA collapses that to a single number without the intermediate list ever being written anywhere. Two optional arguments change what "distinct" means: by_col compares columns instead of rows, and exactly_once returns only the values that appear precisely one time, which is a different question from distinct values and worth keeping straight.
On Excel 2019 and earlier the classic replacement is =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)). It works by counting how many times each value occurs and adding the reciprocals: a value appearing three times contributes 1/3 three times, totalling exactly 1. It gives the same answer as COUNTA(UNIQUE()) on clean data, but it divides by the count of each cell, so a single blank cell means dividing by zero and the whole thing returns #DIV/0!. The blank-safe form is =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&"")).
Both approaches are case-insensitive: Ada and ADA collapse into one value. Both also treat a number and the same number stored as text as different values, which is the usual reason a distinct count comes out higher than a hand count. And the legacy SUMPRODUCT compares every cell in the range against every other cell, so pointing it at a whole column is a way to make Excel stop responding - keep it sized to the rows you actually have.
Syntax
=COUNTA(UNIQUE(array, [by_col], [exactly_once]))| Argument | Required | What it does |
|---|---|---|
array | Required | The range or array to reduce to distinct values. Usually a single column such as A2:A500. A multi-column range returns distinct rows - distinct combinations across the columns - rather than distinct values per column. |
by_col | Optional | Default FALSE, which compares rows and returns distinct rows. Set to TRUE to compare columns instead and return distinct columns, which is what you want on data laid out horizontally. |
exactly_once | Optional | Default FALSE, which returns every distinct value once. Set to TRUE to return only the values that occur exactly one time in the source - the entries with no duplicate anywhere in the range. |
COUNTA's value1 | Required | The UNIQUE result being counted. COUNTA counts every non-empty cell in the spilled array, which is why it gives a value count rather than a row count when UNIQUE is fed more than one column. |
More worked examples
A2:A500 holds the customer name for every order this quarter, and the bottom of the column is padded with empty rows from a template.
=COUNTA(UNIQUE(FILTER(A2:A500, A2:A500<>"")))Returns: 128 - the number of distinct customers, with the empty rows excluded.
FILTER strips the blanks before UNIQUE ever sees them. Without it, UNIQUE emits a single 0 for the empty rows and the count comes back as 129.
The same order list, and you want to know how many customers ordered only once so the retention report can call them out.
=COUNTA(UNIQUE(A2:A500, FALSE, TRUE))Returns: 41 - the customers whose name appears exactly one time.
The third argument changes the question entirely. Distinct customers is 128; customers appearing exactly once is 41. Both are legitimate answers to loosely-worded requests, so confirm which one is wanted.
Excel 2019 with no UNIQUE function, counting distinct order IDs in A2:A200 where a few rows are blank.
=SUMPRODUCT((A2:A200<>"")/COUNTIF(A2:A200, A2:A200&""))Returns: 173 - the same distinct count UNIQUE would give.
The &"" inside COUNTIF is what makes it blank-safe: it stops any blank producing a division by zero. Keep the range tight - this formula does a comparison for every pair of cells, so a whole-column reference is unusable.
Common mistakes
- Blank cells add one to the count
- UNIQUE treats an empty cell as the value 0 and returns it as a distinct entry, so a range sized generously past the data reports one more than it should. Either size the range to the filled rows or filter first: =COUNTA(UNIQUE(FILTER(A2:A500, A2:A500<>""))).
- COUNTA counts cells, not rows, on a multi-column UNIQUE
- =COUNTA(UNIQUE(A2:B200)) returns twice the number of distinct city-and-product pairs, because UNIQUE returns a two-column block and COUNTA counts every cell in it. Use =ROWS(UNIQUE(A2:B200)) when you want the number of distinct combinations.
- The legacy SUMPRODUCT returns #DIV/0! on any blank
- =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)) fails outright as soon as one cell in the range is empty, because COUNTIF returns 0 for a blank criterion and the reciprocal is undefined. The fix is the &"" form: =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&"")).
- Numbers stored as text are counted separately
- An order ID of 1001 typed as a number and the same 1001 imported as text are two distinct values to both UNIQUE and COUNTIF, so the count overstates. Spot it with =SUMPRODUCT(--ISTEXT(A2:A100)) and normalise the column before counting.
- Case differences collapse silently
- UNIQUE, COUNTIF and COUNTA all ignore case, so ADA, Ada and ada are one value. That is usually what you want for names and unwanted for case-sensitive codes. There is no switch for it - if case matters, add a helper column that flags exact duplicates with EXACT and count from that.
Frequently Asked Questions
Use =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&"")). It gives the same distinct count without any dynamic-array support. For very large ranges it will be slow, in which case a PivotTable with Add this data to the Data Model and the Distinct Count summary, or Power Query's Remove Duplicates followed by a row count, are both faster.
The range almost certainly extends past the last row of data. UNIQUE reports empty cells as the value 0 and counts that as one distinct entry. Trim the reference to the filled rows, or wrap the range in FILTER to drop the blanks before UNIQUE sees them.
Pass TRUE as UNIQUE's third argument: =COUNTA(UNIQUE(A2:A100, FALSE, TRUE)) returns the number of entries with no duplicate anywhere in the range. Without it, a value appearing five times still counts as one distinct value. On older Excel the equivalent is =SUMPRODUCT(--(COUNTIF(A2:A100, A2:A100)=1)).
Yes. UNIQUE compares text case-insensitively and keeps whichever spelling it meets first, so a list with both returns one row. If the distinction matters - product codes where case is significant, for example - there is no argument that changes it, and you will need a helper column built on EXACT to identify the case-sensitive duplicates.
Feed both columns to UNIQUE and count rows rather than cells: =ROWS(UNIQUE(A2:B200)). UNIQUE compares whole rows by default, so it returns one row per distinct pair. The older alternative is a helper column of =A2 & "|" & B2 and a distinct count over that, with the pipe chosen because it will not occur inside either value.
Related Tools
Remove Duplicates in Excel
Strip the repeated rows out of an uploaded workbook rather than just counting them.
Find Duplicates in Excel
See which values repeat and how often, without deleting anything.
COUNTA Generator
Count every non-empty cell when distinct values are not what you need.
COUNTIF Generator
Count how many times one particular value occurs in the range.