How to Count Unique Values in Microsoft Excel – How-To Geek

How to

Join 425,000 subscribers and get a daily digest of news, geek trivia, and our feature articles.
By submitting your email, you agree to the Terms of Use and Privacy Policy.
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage. Read more…
Microsoft Excel logo on a green background
You can use the COUNT function and variations to count cells that contain numbers. But what if you want to count only the distinct values in a cell range? We’ll show you two ways to count unique values in Excel.
Both methods we’ll explain use a combination of functions and variations of the COUNT function. And because your cell range could include blank cells, we’re including alternative formulas to account for this.
RELATED: How to Use the COUNT Function in Microsoft Excel
This first method is for those who use Excel for Microsoft 365, Excel for the web, Excel 2021, or Excel for iPhone, iPad, or Android phones or tablets. This is because the UNIQUE function is only available in these and later versions of Excel.
The UNIQUE function provides all distinct values in a cell range. Because you don’t want to list those unique values, but count them instead, you’ll add the COUNTA function. The COUNTA function counts nonblank cells.
To list the unique values in the cell range A2 through A5, you would use this formula:
You can see here we have three distinct values listed.
UNIQUE function in Excel
To count those unique values instead of listing them, you add the COUNTA function to the beginning of the formula:
Now you have the count for those unique values which is 3.
COUNTA with UNIQUE in Excel
If the cell range you want to count contains blanks, those will be included as unique values which may cause a problem. To exclude blank cells in your range, you can add the FILTER function to the formula:
Let’s break down the FILTER portion of the formula. A2:A5,A2:A5 represents the cell range and the criteria to filter which are the same, <> represents not equal to, and "" represents blank.
As you can see in the screenshot below, the blank cell in our array is not counted as a unique value.
COUNTA, UNIQUE, and FILTER functions
RELATED: How to Count Cells in Microsoft Excel
If you’re using a version of Excel where the UNIQUE and FILTER functions are unavailable, you can use SUM and COUNTIF instead.
The SUM function adds numbers and the COUNTIF function counts those cells containing numbers that meet specific criteria.
To find the unique values in the cell range A2 through A5, use the following formula:
To break down this formula, the COUNTIF function counts the cells with numbers in our range and uses that same cell range as the criteria. That result then is divided by 1 and the SUM function adds the remaining values.
SUM and COUNTIF functions in Excel
Similar to the first method, you can run into issues if you have blanks in your cell range with this method. Only, you’ll receive the #DIV/0! error rather than an extra unique value.
To eliminate this problem, you can use the following formula:
The additional portion for the COUNTIF function concatenates a blank string to prevent zeroes in the results because you cannot divide by zero. The additional portion for the SUM function adds the values that do not equal blank. All of this provides the count of distinct values and also does not count blanks as unique.
SUM and COUNTIF filtered
Finding unique values in Excel doesn’t have to be a difficult task. By using the functions and formulas here, you should see those distinct values in no time!
RELATED: Functions vs. Formulas in Microsoft Excel: What’s the Difference?
RSS Feed
The Best Free Tech Newsletter Anywhere
By submitting your email, you agree to the Terms of Use and Privacy Policy.


Leave a Reply

Your email address will not be published.