=COUNTA(UNIQUE(A2:A9)) counts how many different values are in A2:A9. UNIQUE returns each value once, and COUNTA counts that list. It needs Excel 2021 or Microsoft 365; older versions are covered below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Unique list | Count | |
| 2 | Ana | Ana | 5 | |
| 3 | Ben | Ben | ||
| 4 | Ana | Cara | ||
| 5 | Cara | Dan | ||
| 6 | Ben | Eva | ||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
Eight orders came from five customers. C2 spills the list of UNIQUE names so you can see what is being counted, and D2 counts it without needing the list on the sheet. Change A9 to Ana and the count drops to 4; type a new name and it goes up.
UNIQUE ignores case, so Ana and ana count as one customer.
Count unique values in older Excel
Excel 2019 and earlier have no UNIQUE. The classic formula is:
=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))
COUNTIF with the whole range as its criteria returns, for every row, how many times that row's value appears. A name that appears 3 times gets 3 on each of its rows, so 1/3 is added three times and the name adds up to exactly 1. Column B shows the count for each row and column C the fraction.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Times | 1/Times | Count | |
| 2 | Ana | 3 | 0.33 | 5 | |
| 3 | Ben | 2 | 0.50 | 5.00 | |
| 4 | Ana | 3 | 0.33 | ||
| 5 | Cara | 1 | 1.00 | ||
| 6 | Ben | 2 | 0.50 | ||
| 7 | Dan | 1 | 1.00 | ||
| 8 | Ana | 3 | 0.33 | ||
| 9 | Eva | 1 | 1.00 |
Ana's three rows each add 0.33, Ben's two rows each add 0.50, and the three single names add 1 each: 5 in total, the same as the helper column's SUM. On tens of thousands of rows this formula is slow, because COUNTIF scans the whole range once per row; UNIQUE does not have that cost.
Distinct vs unique: values that appear only once
"Unique" is used for two different counts. The one above counts distinct values: every name once. The other counts the values that appear exactly once, such as customers who ordered only one time. UNIQUE does that with its third argument, exactly_once, set to TRUE.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Count | Result | |
| 2 | Ana | Distinct | 5 | |
| 3 | Ben | Exactly once | 3 | |
| 4 | Ana | Exactly once, older Excel | 3 | |
| 5 | Cara | |||
| 6 | Ben | |||
| 7 | Dan | |||
| 8 | Ana | |||
| 9 | Eva |
Five distinct customers, but only three of them, Cara, Dan and Eva, ordered once. The older-Excel version counts the rows whose COUNTIF is exactly 1. If every value repeats, UNIQUE with exactly_once returns #CALC! and COUNTA counts that error as 1; the SUMPRODUCT version gives 0.
Count unique values with a condition
To count the different customers in one region, filter the rows first and then count what is left. FILTER keeps the North rows and UNIQUE removes the repeats.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Region | Region | Customers | |
| 2 | Ana | North | North | 3 | |
| 3 | Ben | South | South | 3 | |
| 4 | Ana | North | North, older Excel | 3 | |
| 5 | Cara | North | |||
| 6 | Ben | North | |||
| 7 | Dan | South | |||
| 8 | Ana | North | |||
| 9 | Eva | South |
North has five orders from three customers: Ana, Cara and Ben. E3 counts South the same way. E4 is the version for Excel 2019 and earlier: COUNTIFS counts each customer and region pair, and the condition keeps only the North fractions.
If no row matches, FILTER returns #CALC!, and COUNTA counts that error as one value: type West in D2 and E2 shows 1, not 0. Wrapping the formula in IFERROR does not help, because COUNTA returns no error. Count the rows of the result instead, which does pass the error on: =IFERROR(ROWS(UNIQUE(FILTER(A2:A9,B2:B9="West"))),0) returns 0.
Count unique values and ignore blanks
An empty cell in the range becomes one more "value". UNIQUE returns it as a 0 and COUNTA counts that 0, so for Ana, an empty cell, Ben, Ana, an empty cell, Cara and Ben, Excel gives:
=COUNTA(UNIQUE(A2:A8)) 4 three names plus the 0 for the empty cells
In the older formula an empty row makes COUNTIF return 0, so 1/0 gives #DIV/0!. Remove the blanks first:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Formula | Count | |
| 2 | Ana | Skip blanks | 3 | |
| 3 | Older Excel | 3 | ||
| 4 | Ben | |||
| 5 | Ana | |||
| 6 | ||||
| 7 | Cara | |||
| 8 | Ben |
Both formulas count the three customers. FILTER with A2:A8<>"" drops the empty cells before UNIQUE sees them. In the older formula, A2:A8&"" turns each empty cell into an empty string so COUNTIF never returns 0, and (A2:A8<>"") gives those rows a weight of 0.
Practice: count the products
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Count | Result | |
| 2 | 1001 | Apple | Products | ||
| 3 | 1002 | Pear | |||
| 4 | 1003 | Apple | |||
| 5 | 1004 | Plum | |||
| 6 | 1005 | Pear | |||
| 7 | 1006 | Apple | |||
| 8 | 1007 | Plum | |||
| 9 | 1008 | Fig |
Your turn: Count how many different products appear in B2:B9. Write the formula in E2.
Which formula for your Excel
| Count | Excel 365 / 2021 | Excel 2019 and earlier |
|---|---|---|
| Distinct values | =COUNTA(UNIQUE(A2:A9)) | =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)) |
| Values that appear once | =COUNTA(UNIQUE(A2:A9,,TRUE)) | =SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)=1)) |
| Distinct, with a condition | =COUNTA(UNIQUE(FILTER(A2:A9,B2:B9="North"))) | =SUMPRODUCT((B2:B9="North")/COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9)) |
| Distinct, skipping blanks | =COUNTA(UNIQUE(FILTER(A2:A9,A2:A9<>""))) | =SUMPRODUCT((A2:A9<>"")/COUNTIF(A2:A9,A2:A9&"")) |
In a pivot table, the Distinct Count summary does the same job without a formula, but only when the pivot table is created with "Add this data to the Data Model" ticked. To delete the repeats rather than count them, see remove duplicates.
Frequently Asked Questions
How do I count unique values in Excel?
In Excel 365 or 2021, use =COUNTA(UNIQUE(A2:A9)): UNIQUE lists each value once and COUNTA counts the list. In older versions use =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)).
How do I count values that appear only once?
Set UNIQUE's third argument, exactly_once, to TRUE: =COUNTA(UNIQUE(A2:A9,,TRUE)). For Ana, Ana, Ben it gives 1, since only Ben appears once. In Excel 2019 and earlier use =SUMPRODUCT(--(COUNTIF(A2:A9,A2:A9)=1)).
How do I count unique values with a condition?
Filter first, then count: =COUNTA(UNIQUE(FILTER(A2:A9,B2:B9="North"))) counts the different customers in the North rows. If no row matches, COUNTA counts FILTER's #CALC! error as 1, so when that can happen use =IFERROR(ROWS(UNIQUE(FILTER(A2:A9,B2:B9="North"))),0).
How do I count unique values and ignore blank cells?
Remove the blanks before UNIQUE: =COUNTA(UNIQUE(FILTER(A2:A9,A2:A9<>""))). In older Excel, =SUMPRODUCT((A2:A9<>"")/COUNTIF(A2:A9,A2:A9&"")) skips them.
