You need to count only the distinct items in a list, but the standard COUNT function includes every duplicate. Excel does not have a single, dedicated function for counting unique values. You must combine specific functions in a formula to filter out repeated entries. This article explains how to build formulas to count unique text, numbers, or a combination of both.
Key Takeaways: Counting Unique Values in Excel
- UNIQUE and COUNTA functions: Use this combination in Excel for Microsoft 365 to easily list and count distinct items.
- SUM, IF, and COUNTIF array formula: Counts unique values in older Excel versions by checking for the first occurrence of each item.
- FREQUENCY function: Provides the fastest method for counting unique numerical values only in a range.
Understanding Excel’s Unique Value Formulas
A unique value is an item that appears only once in a specified range. Counting these values requires Excel to identify each distinct entry and tally it just one time. The method you use depends on your version of Excel and the type of data you have. Excel for Microsoft 365 and Excel 2021 include the dynamic UNIQUE function, which simplifies the task. Older versions require array formulas that use functions like SUM, IF, and COUNTIF together.
For lists containing only numbers, the FREQUENCY function offers a specialized and efficient counting method. All these formulas ignore blank cells by default, which is usually the desired behavior. Understanding these different approaches lets you choose the right tool for your specific worksheet.
Steps to Count Unique Values in Excel
Follow the method that matches your version of Excel and data type.
Method 1: Using UNIQUE and COUNTA (Excel for Microsoft 365/2021)
This is the simplest method for modern Excel versions. The UNIQUE function extracts a list of distinct values, and COUNTA counts them.
- Select the output cell
Click on an empty cell where you want the unique count to appear. - Enter the combined formula
Type =COUNTA(UNIQUE(range)). Replace ‘range’ with your actual cell range, like A2:A100. - Press Enter
Excel calculates and displays the number of unique values in the selected range. The formula updates automatically if the source data changes.
Method 2: Using SUM, IF, and COUNTIF (All Excel Versions)
This older array formula works in any version of Excel and handles both text and numbers.
- Select the output cell
Click on an empty cell for the result. - Enter the array formula
Type =SUM(IF(range<>“”,1/COUNTIF(range, range),0)). Replace ‘range’ with your cell range. - Confirm as an array formula
In older Excel versions, press Ctrl+Shift+Enter instead of just Enter. Curly braces { } will appear around the formula. In Excel 365, pressing Enter is sufficient.
Method 3: Using FREQUENCY for Numbers Only
The FREQUENCY function is designed for numbers and is computationally efficient for large datasets.
- Select the output cell
Choose an empty cell for the count result. - Enter the FREQUENCY formula
Type =SUM(IF(FREQUENCY(range, range)>0,1)). Replace ‘range’ with your range of numbers. - Confirm the formula
Press Ctrl+Shift+Enter in older Excel versions to create the array formula. Press Enter in Excel 365.
Common Mistakes and Formula Limitations
Formula Returns a #SPILL! Error
This error occurs in Excel 365 when the UNIQUE function tries to output a list but there is not enough empty space below the formula cell. Clear the cells directly beneath your formula or move the formula to a location with more blank rows.
Count Includes Blank Cells as a Unique Value
If your range includes truly empty cells, the standard formulas ignore them. However, if cells contain formulas that return an empty string (“”), they may be counted. Modify the SUM/IF/COUNTIF formula to =SUM(IF(range<>“”,1/COUNTIF(range, range))). This explicitly excludes empty strings.
Formula Counts Numbers and Text Differently
The FREQUENCY method will ignore text entries and count only numbers. If your data is a mix of text and numbers, use either the UNIQUE or the SUM/IF/COUNTIF method for an accurate total count of all unique entries.
Unique Counting Methods Comparison
| Item | UNIQUE & COUNTA | SUM, IF, COUNTIF Array | FREQUENCY Function |
|---|---|---|---|
| Excel Version | Excel for Microsoft 365, Excel 2021 | All versions | All versions |
| Data Type | Text and numbers | Text and numbers | Numbers only |
| Ease of Use | Simple, single function | Complex array entry | Moderate array entry |
| Performance | Fast, dynamic | Slower on large ranges | Very fast for numbers |
| Handles Blanks | Ignores them automatically | Requires formula tweak | Ignores them automatically |
You can now accurately count distinct entries in any list. Use the UNIQUE function for modern Excel or the classic array formula for compatibility. For your next task, try using the UNIQUE function by itself to generate a spill range listing all the distinct values. Remember that pressing F9 in the formula bar lets you evaluate parts of a complex array formula to see intermediate results.