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

Use the COUNTA and UNIQUE Functions

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:

=UNIQUE(A2:A5)
Advertisement

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:

=COUNTA(UNIQUE(A2:A5))

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:

Note: The FILTER function is only available in the versions of Excel listed above.
=COUNTA(UNIQUE(FILTER(A2:A5,A2:A5<>"")))

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

Use the SUM and COUNTIF Functions

If you’re using a version of Excel where the UNIQUE and FILTER functions are unavailable, you can use SUM and COUNTIF instead.

Note: You may also use the same formulas below replacing the SUM function with SUMPRODUCT.
Advertisement

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:

=SUM(1/COUNTIF(A2:A5,A2:A5))

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:

=SUM((A2:A5<>"")/COUNTIF(A2:A5,A2:A5&""))
Advertisement

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?

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
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 Full Bio »