The simplest Excel formula for counting unique cells is =SUMPRODUCT(1/COUNTIF(range, range)), which works in all versions of Excel. For Microsoft 365 or Excel 2021, the cleaner and faster formula is =COUNTA(UNIQUE(range)), which directly returns the number of distinct values.
What “Unique” Means in Excel
Excel users often mix up two concepts. Unique values are all distinct entries in a list—every item that appears at least once. Distinct values are the same thing in most Excel contexts, but unique-only values (a third concept) are entries that appear exactly once. For example, in the list A, B, A, C, the unique values are A, B, C (3), but the values that appear only once are B, C (2). This article focuses on counting distinct entries, which is what most people mean when they ask for “unique cells.”
The Classic Formula: SUMPRODUCT with COUNTIF
Before Excel 365 introduced the UNIQUE function, the standard way to count unique values was an array formula using COUNTIF. Assume your data is in A2:A10 (with no blank cells). The formula is:
=SUMPRODUCT(1/COUNTIF(A2:A10, A2:A10))
How it works: COUNTIF returns an array of counts for each cell—for example, if “Apple” appears 3 times, it returns 3 for each of those cells. Dividing 1 by those counts gives fractions (1/3, 1/3, 1/3). Summing those fractions with SUMPRODUCT yields 1 for each distinct item. In the list Apple, Banana, Apple, Cherry, COUNTIF returns {3,1,3,1}, 1/COUNTIF gives {0.333,1,0.333,1}, and the sum is 2.666, which rounds to 3—the correct count of distinct values.
This formula works in every Excel version, but it has a serious flaw: if any cell in the range is blank, COUNTIF treats blanks as a value and returns a count, but dividing by that count produces a #DIV/0! error because 1/0 is undefined. To avoid this, restrict the range to non-blank cells or use a more robust formula (see below).
The Modern Formula: UNIQUE and COUNTA
If you have Excel 2021 or Microsoft 365, the UNIQUE function simplifies everything. The formula is:
=COUNTA(UNIQUE(A2:A10))
Here, UNIQUE returns a list of all distinct values from A2:A10, and COUNTA counts the non-empty cells in that list. For the same Apple, Banana, Apple, Cherry example, UNIQUE returns Apple, Banana, Cherry (vertically), and COUNTA returns 3.
This formula ignores blank cells automatically because UNIQUE does not include blanks in its output. It also handles text, numbers, and mixed types. If your data contains numbers stored as text, UNIQUE treats them as different—so 123 (number) and "123" (text) are counted separately. That is usually what you want, but be aware of it.
If you need to count unique values that meet a condition (e.g., only sales over $100), use COUNTA(UNIQUE(FILTER(range, condition))). For example, with amounts in B2:B10 and regions in A2:A10, to count unique regions with sales over 100: =COUNTA(UNIQUE(FILTER(A2:A10, B2:B10>100))). This requires Excel 365.
How to Choose the Right Formula
- Use
COUNTA(UNIQUE())if you have Excel 2021 or 365—it is simpler, faster, and ignores blanks. - Use
SUMPRODUCT(1/COUNTIF())if you are on Excel 2019 or earlier, but only if your range has no blank cells. - Use
=SUM(IF(FREQUENCY(MATCH(range,range,0),ROW(range)-ROW(first_cell)+1)>0,1))(entered with Ctrl+Shift+Enter in older versions) if you need to handle blanks and are stuck on an old version. This is an array formula that counts numeric and text values, ignoring blanks.
For a quick one-off, you can also use the Remove Duplicates feature (Data > Remove Duplicates) to see the count in the status bar, but that alters your data.
Common Problems and Mistakes
The SUMPRODUCT formula fails with blank cells, as mentioned. Another issue is that it counts 0 and empty text ("") as unique values. If your data has formulas that return "", those will be counted as a distinct item. To exclude blanks and empty strings, use a more complex array formula: =SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")). This appends an empty string to the criteria, which makes COUNTIF ignore true blanks but still count empty strings—so it may still count "" as one value.
Another common mistake is using COUNTIF without SUMPRODUCT, like =COUNTIF(A2:A10,"unique"), which counts cells that literally contain the word “unique”, not distinct values. Also, remember that UNIQUE is case-insensitive—Apple and apple are considered the same. If you need case-sensitive counting, you need a more advanced array formula using SUMPRODUCT and EXACT, but that is rarely necessary.
Comparing with PivotTables and Power Query
If you prefer a non-formula approach, a PivotTable can show distinct counts. Drag a field into the Values area, then change the value field settings to “Distinct Count” (available in Excel 2013 and later). This is useful for large datasets because it does not require a formula, but it does not update automatically when you add new rows unless you refresh the PivotTable.
Power Query (Get & Transform Data) is another alternative. You can load your data, use the “Remove Duplicates” transformation, and then count the rows. This is a manual process but works well for one-time analysis or when you need to clean data first.
Performance and Large Data
The SUMPRODUCT(1/COUNTIF) formula is notoriously slow on large datasets because it performs a full range scan for each cell. On 100,000 rows, it can freeze Excel. The UNIQUE function is much faster because it is implemented in C++ and optimized. If you have a large dataset and an older Excel, consider using a PivotTable or Power Query instead of a volatile array formula.
Also, note that UNIQUE returns a dynamic array that spills into adjacent cells. If you place the formula in a cell that has data below or to the right, you will get a #SPILL! error. Clear those cells or move the formula.
Frequently Asked Questions
How do I count unique text values only?
Use =COUNTA(UNIQUE(FILTER(A2:A10, ISTEXT(A2:A10)))) in Excel 365. This filters to text values, then counts unique ones. In older versions, use an array formula like =SUMPRODUCT((ISTEXT(A2:A10))/COUNTIF(A2:A10,A2:A10&"")), but it may count empty strings as text.
How do I count unique values with multiple criteria?
Use =COUNTA(UNIQUE(FILTER(A2:A10, (B2:B10="West")*(C2:C10>100)))) in Excel 365. The multiplication acts as AND. For OR, use + instead of *. In older versions, you would need a complex array formula with MATCH and FREQUENCY.
Why does my UNIQUE formula return #SPILL! error?
Because the dynamic array has no room to spill. There is data in the cells where the results would appear. Select the formula cell, and you will see a dashed border around the spill range. Delete or move the blocking data, or use @ to return only the first value (not recommended for counting).
Can I count unique values in a column with blanks?
Yes, =COUNTA(UNIQUE(A2:A100)) ignores blanks automatically. If you use the classic SUMPRODUCT formula, it will error with blanks, so use the FREQUENCY array formula or the UNIQUE version.
Does UNIQUE work in Excel 2019?
No. UNIQUE is only available in Excel 2021 and Microsoft 365. In Excel 2019, use SUMPRODUCT(1/COUNTIF) or a PivotTable with Distinct Count.
How do I count unique numbers only?
Use =COUNTA(UNIQUE(FILTER(A2:A10, ISNUMBER(A2:A10)))) in Excel 365. For older versions, use =SUMPRODUCT((ISNUMBER(A2:A10))/COUNTIF(A2:A10,A2:A10&"")), but test it because it may count numbers as text if they are formatted as text.
Related
- JavaScript String substring() Explained
- Splunk Split Command: How to Use It
- Python split() and join() Explained with Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Turn a Vertical List Into a Horizontal RowTo turn a vertical list into a horizontal row in Excel for Microsoft 365, enter =TRANSPOSE(A2:A6) in the…
- Excel MONTH Function: Extract the Month from a DateTo get the month number from a date in Excel, enter =MONTH(A2) in another cell, assuming A2 contains…
- Excel RIGHT Function Examples: Extract Text from the EndUse Excel’s RIGHT function to return characters from the end of a text value. For example, if A2…
- VLOOKUP Examples for Beginners: A Practical Excel GuideIn Excel, VLOOKUP finds a value in the first column of a range and returns related information from…
- Freeze Values in Excel: Turn Formulas Into Fixed ResultsTo freeze values in Excel, select the formula cells, copy them, then choose Home > Paste > Paste…
- Excel XLOOKUP Sort Order: How Search Direction WorksIn Excel, XLOOKUP does not have a separate sort-order argument: its sixth argument, search_mode, controls the direction or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.