Count Unique Values in Excel and Google Sheets
Purpose
Counting unique values is a question I get asked constantly — how many distinct customers placed orders this month, how…
Counting unique values is a question I get asked constantly — how many distinct customers placed orders this month, how many different products were sold, how many unique departments are in the payroll. It sounds like it should be a built-in function and in Google Sheets it is: COUNTUNIQUE. In Excel 365 it takes two nested functions: COUNTA wrapped around UNIQUE. In older Excel, it requires a SUMPRODUCT trick that looks intimidating but works reliably once you understand it. I will cover all three approaches here so you have the right formula regardless of which version or application you are using. This is one of those formulas that once you have it in your toolkit you pull out weekly.
Syntax
✓ Excel 365 (UNIQUE) / Excel 2003+ (SUMPRODUCT)
✓ Google Sheets
=COUNTA(UNIQUE(range))
For Excel versions before 365, use =SUMPRODUCT(1/COUNTIF(range,range)) as the UNIQUE function is not available. Google Sheets also supports the COUNTA(UNIQUE()) approach.
Arguments
| Argument | Required | Description |
|---|---|---|
| range | Required | The range or column containing the values to count unique entries in. Can include duplicates — the formula handles deduplication. For SUMPRODUCT approach the range must not contain blanks without additional handling. |
| criteria (FILTER version) | Optional | When counting unique values subject to a condition, add FILTER inside UNIQUE to filter the range before counting unique values. Requires Excel 365 or Google Sheets. |
How it works
The approach depends on which version you are using. In Excel 365, UNIQUE(range) extracts a list of distinct values from the range as a dynamic array. COUNTA then counts how many items are in that list. Together they give you the count of distinct values. In Google Sheets, COUNTUNIQUE does the same thing in one step. In older Excel without UNIQUE, the SUMPRODUCT trick works by using COUNTIF to count how many times each value appears in the range. Dividing 1 by the count gives the reciprocal — a value that appears three times contributes 1/3 three times, which sums to 1. A value appearing once contributes 1 once. SUMPRODUCT adds all these fractions and the total equals the number of unique values. It sounds complex but it reliably returns the right answer.
Examples
1
Count how many distinct departments exist in the employee list.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Department | Unique Depts |
| 2 | Alice | Sales | =COUNTA(UNIQUE(B2:B11)) |
| 3 | Bob | Marketing | |
| 4 | Carol | Sales | |
| 5 | David | Engineering | |
| 6 | Eve | Marketing | |
| 7 | Frank | HR | |
| 8 | Grace | Sales | |
| 9 | Hank | Engineering | |
| 10 | Iris | HR | |
| 11 | James | Sales |
Row 2: 4
— Four distinct departments: Sales, Marketing, Engineering, HR. Even though Sales appears four times it counts once.
UNIQUE extracts [Sales, Marketing, Engineering, HR] as a four-item list. COUNTA counts the four items. The formula ignores how many times each department appears.
2
Use the SUMPRODUCT approach to count unique customers — compatible with all Excel versions.
| A | B | C | |
|---|---|---|---|
| 1 | Customer | Order Value | Unique Customers |
| 2 | Acme Corp | 1200 | =SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11)) |
| 3 | Beta Ltd | 850 | |
| 4 | Acme Corp | 2100 | |
| 5 | Gamma Inc | 450 | |
| 6 | Delta Co | 3200 | |
| 7 | Beta Ltd | 1100 | |
| 8 | Acme Corp | 750 | |
| 9 | Epsilon Plc | 980 | |
| 10 | Gamma Inc | 1600 | |
| 11 | Delta Co | 2800 |
Row 2: 5
— Five distinct customers: Acme Corp, Beta Ltd, Gamma Inc, Delta Co, Epsilon Plc. The SUMPRODUCT approach gives the same answer as COUNTA(UNIQUE()) but works in all Excel versions.
Acme Corp appears 3 times so COUNTIF returns 3 for each of those rows. 1/3 + 1/3 + 1/3 = 1. Beta Ltd appears twice: 1/2 + 1/2 = 1. Each unique value contributes exactly 1 to the total regardless of how many times it appears.
Common use cases
1. Count how many distinct customers placed orders in a given month
=COUNTA(UNIQUE(A2:A100))
2. Count unique product codes in a sales log to see how many different items sold
=COUNTUNIQUE(B2:B500)
3. Count unique suppliers from a procurement list
=COUNTA(UNIQUE(C2:C200))
4. Count how many different error types appear in a log file
=SUMPRODUCT(1/COUNTIF(D2:D50,D2:D50))
5. Count unique values in a filtered result — unique departments in the Sales region only
=COUNTA(UNIQUE(FILTER(B2:B100,A2:A100="Sales")))
Common errors
#DIV/0! in SUMPRODUCT formula
The range contains blank cells. COUNTIF counts blank cells with a count greater than zero but in rare cases causes division issues. The SUMPRODUCT(1/COUNTIF()) formula breaks when any cell in the range is blank.
Fix: Add a check to exclude blanks: =SUMPRODUCT((1/COUNTIF(A2:A11,A2:A11))*(A2:A11<>"")) — the second part evaluates to 0 for blank rows, effectively excluding them.
#NAME? error
The UNIQUE function is not available in your Excel version. UNIQUE was introduced in Excel 365 and is not available in Excel 2019 or earlier.
Fix: Use =SUMPRODUCT(1/COUNTIF(range,range)) instead which works in all Excel versions. Or upgrade to Excel 365.
COUNTA counts one extra row
The range includes a header row and UNIQUE includes the header text as one of the unique values.
Fix: Start the range from the first data row, not the header: =COUNTA(UNIQUE(B2:B100)) not =COUNTA(UNIQUE(B1:B100)).
Tips and variations
Use SUMPRODUCT for compatibility across all Excel versions
If your file will be opened by colleagues using older Excel versions, use =SUMPRODUCT(1/COUNTIF(range,range)) rather than COUNTA(UNIQUE()). It works in Excel 2003 through to 365 and produces identical results.
=SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11))
Filter before counting for conditional unique counts
When you want unique values within a subset — unique customers who bought product X, unique regions with sales above 10000 — wrap FILTER inside UNIQUE before counting. This is only available in Excel 365 and Google Sheets.
=COUNTA(UNIQUE(FILTER(A2:A100,B2:B100="Sales")))
Use UNIQUE to list unique values then COUNTA to count them
UNIQUE on its own returns the list of distinct values as a spilled array. COUNTA(UNIQUE()) counts them. Understanding this helps when you need both the list and the count — put UNIQUE in one cell to get the list and COUNTA(UNIQUE()) in another for the count.
=UNIQUE(B2:B100)
Excel vs Google Sheets
Excel vs Google Sheets
Google Sheets has COUNTUNIQUE which makes this a single-function operation. Excel requires COUNTA(UNIQUE()) in Excel 365 or the SUMPRODUCT workaround in older versions. The UNIQUE function is available in both Excel 365 and Google Sheets with identical syntax. For maximum compatibility across versions, the SUMPRODUCT approach works everywhere.
Frequently asked questions
In Excel 365 use =COUNTA(UNIQUE(range)). UNIQUE extracts the distinct values and COUNTA counts how many there are. In older Excel versions use =SUMPRODUCT(1/COUNTIF(range,range)) which counts how many times each value appears and sums the reciprocals — a value appearing twice contributes 1/2 + 1/2 = 1. In Google Sheets COUNTUNIQUE(range) does it directly.
COUNTUNIQUE is a Google Sheets function and does not exist in Excel. In Excel use =COUNTA(UNIQUE(range)) if you have Excel 365, or =SUMPRODUCT(1/COUNTIF(range,range)) for older versions.
In Excel 365: =COUNTA(UNIQUE(FILTER(range,condition_range=condition))). This filters first then counts unique values in the filtered result. For example =COUNTA(UNIQUE(FILTER(A2:A100,B2:B100="Sales"))) counts unique employee names in the Sales department. In older Excel versions this requires a complex SUMPRODUCT formula.
COUNTIF counts blank cells too — when a cell is blank, COUNTIF returns a count greater than zero, but 1 divided by that count still gives a small fraction. The real problem is when COUNTIF returns zero for some reason, causing #DIV/0!. If the range has empty cells use SUMPRODUCT((1/COUNTIF(range,range))*(range<>"")) to exclude blanks from the calculation.
In Excel 365 and Google Sheets use =UNIQUE(range) to extract the list of unique values. UNIQUE returns a dynamic array that spills downward. Sort the results with =SORT(UNIQUE(range)) for alphabetical ordering. This is separate from counting — UNIQUE gives you the list, COUNTA(UNIQUE()) gives you the count.