To count unique values in Excel, use =SUMPRODUCT((COUNTIF(range,range)=1)*1) for values appearing exactly once, or =SUMPRODUCT(1/COUNTIF(range,range)) for all distinct values (works with numbers and text, but not blanks). For a modern, robust method, use =COUNTA(UNIQUE(range)) in Excel 365 or Excel 2021, which handles blanks and returns the count of distinct entries.
Understanding Unique vs. Distinct
Excel users often confuse these terms. “Unique” values are those that appear only once in a list. “Distinct” values are all different values, ignoring how many times each appears. For example, in the list {A, A, B, C}, the unique values are B and C (count = 2), while the distinct values are A, B, and C (count = 3). Most people asking “count unique values” actually want the distinct count. The formulas below clarify which method gives which result.
Method 1: The Classic SUMPRODUCT/COUNTIF Formula
This method works in all Excel versions. Assume your data is in cells A2:A20 (adjust the range as needed).
- Distinct count (all different values):
=SUMPRODUCT(1/COUNTIF(A2:A20,A2:A20)) - Unique count (values appearing only once):
=SUMPRODUCT((COUNTIF(A2:A20,A2:A20)=1)*1)
How it works: COUNTIF returns an array of counts for each cell. For distinct, dividing 1 by each count gives fractions that sum to the number of distinct items (e.g., if “A” appears 3 times, you get 1/3 + 1/3 + 1/3 = 1). For unique, the (COUNTIF=1) part turns each cell into TRUE/FALSE, and multiplying by 1 converts to 1/0, which SUMPRODUCT sums.
Caution: If your range contains blank cells, 1/COUNTIF returns a #DIV/0! error because COUNTIF counts blanks as 0. To avoid this, use =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")) – this appends an empty string to the criteria to handle blanks gracefully.
Method 2: The Modern UNIQUE Function (Excel 365/2021)
If you have Excel for Microsoft 365 or Excel 2021, the UNIQUE function is the simplest and most reliable. It returns a dynamic array of distinct values. To count them directly:
- Distinct count:
=COUNTA(UNIQUE(A2:A20)) - Unique count (only once):
=COUNTA(UNIQUE(A2:A20,,TRUE))– the third argument (TRUE) tells UNIQUE to return items that appear exactly once.
Note: COUNTA counts non-empty cells, so blanks in the original range are ignored. If you need a count that includes blanks as a distinct value, you’d need a different approach, but that’s rarely desired.
Method 3: PivotTable for a Quick Visual Count
A PivotTable is a no-formula way to see distinct counts, but it requires a bit of setup. In Excel 2013 and later, you can add a field to the Values area and set it to “Distinct Count” (available when you add the same field again and change the value field settings). Steps:
- Select your data range and go to Insert > PivotTable.
- Drag the field you want to count into both the Rows and Values areas.
- Click the dropdown arrow next to the Values field, choose “Value Field Settings”, then select “Distinct Count”.
This method is great for large datasets because PivotTables are optimized for performance. However, it doesn’t give a single cell formula result; you get a table that updates when you refresh.
Common Errors and How to Fix Them
- #DIV/0! error in the classic formula: Caused by blank cells or cells with empty text. Use the modified formula with
&""as shown above. - #NAME? error: The
UNIQUEfunction is not available in your Excel version (pre-2021). Use the SUMPRODUCT method. - Formula returns 0: Check that your range is correct and doesn’t include the header row if you don’t want it counted. Also, ensure the range doesn’t contain errors or empty strings that might be counted.
- Dynamic array spills: If you use
UNIQUEand see a #SPILL! error, it means something is blocking the output cells. Clear them or move the formula to an empty area.
Counting Unique Values with Multiple Criteria
Sometimes you need to count unique values that meet a condition. For example, count distinct products sold in the “East” region. With Excel 365, you can use UNIQUE combined with FILTER:
Assume products in A2:A20, regions in B2:B20. To count distinct products in “East”:
=COUNTA(UNIQUE(FILTER(A2:A20,B2:B20="East")))
For older Excel, you’d use a more complex array formula (entered with Ctrl+Shift+Enter in pre-dynamic versions):
=SUMPRODUCT((B2:B20="East")*(COUNTIFS(A2:A20,A2:A20,B2:B20,"East")=1))
This counts only those East rows whose product appears exactly once in the East subset.
Frequently Asked Questions
How do I count unique values in a column with blanks?
Use the UNIQUE function with COUNTA – it ignores blanks automatically. For the classic formula, use the blank-safe version: =SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")). This treats blank cells as non-counted.
Can I count unique values in multiple columns at once?
Yes, in Excel 365 you can use =COUNTA(UNIQUE(A2:B20)) to count unique combinations of rows across two columns. For older versions, you’d need to concatenate columns with a helper column (e.g., =A2&"-"&B2) and then count unique values in that helper.
Why does my UNIQUE function return #VALUE!?
This usually happens if the range contains errors or if you’re using the function in a version that doesn’t support it. Check that your Excel is updated to Microsoft 365 or 2021, and ensure the range doesn’t contain error values like #N/A. If it does, wrap the range with IFERROR inside UNIQUE, but that can be tricky.
What is the fastest way to count unique values in a very large dataset?
For large data (tens of thousands of rows), the UNIQUE function is efficient because it’s a native dynamic array. For even larger data, consider using a PivotTable with “Distinct Count”, which is optimized for performance. Avoid the SUMPRODUCT/COUNTIF method on huge ranges as it can be slow.
Does the COUNTIF method work with numbers and text?
Yes, COUNTIF treats numbers and text differently but works for both. However, note that COUNTIF is case-insensitive for text (e.g., “apple” and “APPLE” are counted as the same). If you need case-sensitive counting, you’d need a more complex array formula with EXACT.
Related
- String Functions in Google Sheets: A Practical Guide
- How to Add Speaker Notes in PowerPoint
- Balance of Trade Data: What It Is and How to Read It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Round Negative Numbers to Zero in ExcelIf your values are in A2 and you want every negative number to become zero while leaving zero…
- Days Left in Month in Excel: Formula and Examples for DatesIf the date is in A2, use =EOMONTH(A2,0)-A2 to count the days remaining after that date, not including…
- Excel SUBTOTAL Function: Codes and ExamplesExcel’s SUBTOTAL function calculates a chosen summary—such as a sum, average, count, minimum, or maximum—and automatically ignores rows…
- Calculate Age in Excel with DATEDIF: Formula and TipsTo calculate someone’s age in completed years in Excel, enter their birth date in A2 and use =DATEDIF(A2,TODAY(),"Y").…
- Text Formula Examples in Excel: Clean, Extract, CombineExcel text formulas let you combine words, extract part of a value, standardize capitalization, and clean imported text.…
- Excel Split Cells With Regex: Formulas and MethodsYes—Excel for Microsoft 365 can split text using a regular expression by replacing regex-matched separators with a marker…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.