How to use the UNIQUE() function to return a count of unique values in Excel

When counting unique values in Microsoft Excel, use UNIQUE() as a helper function and avoid more complicated expressions.

The TechRepublic article How to create a sorted unique list in an Excel spreadsheet shows you how to use Excel’s UNIQUE() dynamic array function to create a unique list. It’s easy and it’s dynamic, as long as the original data is in a Table object. A list isn’t, however, a count, so in this article, I’ll show you how to use UNIQUE() to return the count of unique values, indirectly, in Microsoft Excel. First, we’ll review how UNIQUE() returns a unique list. Then, we’ll count the items in that list. This route is much easier than an expression that evaluates the source data directly.

SEE: Software Installation Policy (TechRepublic Premium)

I’m using Microsoft 365 on a Windows 10 64-bit system. UNIQUE() is available only in Microsoft 365, Excel for the Web, and Excel for Android tablets and phones. For your convenience, you can download the .xlsx demonstration file.

How to use UNIQUE() in Excel

The dynamic array function, UNIQUE() has been around for a while, so it’s possible that you already know how to use it. If that’s the case, feel free to skip this section. As a stand-alone function, UNIQUE() returns a list of unique values using the syntax

UNIQUE(array, [by_col], [exactly_once])

The array argument is the Excel range you want to reduce to a unique list. The by_col argument is a Boolean value: TRUE compares columns and returns unique columns; FALSE is the default and will compare rows against rows and returns unique rows. The exactly_once argument is also a Boolean value: TRUE returns all distinct rows or columns that occur exactly once from the range or array; FALSE, the default, returns all distinct rows or columns from the range.

How to use COUNTA() in Excel

We know that UNIQUE() returns a list of unique values, but we want a count of those values, not a list. Figure A shows a simple data set in a Table object and a list of unique names—the result of a UNIQUE() function that uses only one argument, the array argument:

H3: =SORT(UNIQUE(D3:D13))

Figure A

UNIQUE()retrieves the unique values and passes them to SORT(), which returns them in alphabetical order. Without the dynamic array function, SORT(), UNIQUE() returns an unsorted list. This example is easy to follow and if you’d like to learn more about Excel’s UNIQUE() function, read the linked article in the introduction.

I mentioned that the data is in a Table object—this is important if you want the functions to update automatically.

How to use COUNTA() to count the items in the unique list in Excel

We have the list, so all we need to do is count the number of items in that list using COUNTA() as follows:

J3: =COUNTA(H3#)

Figure B shows the results—the list has six items, and it just so happens that all of the values in the list are unique.

Figure B

How easy was that? Without that unique list, the solution is much more complex.

Excel’s COUNTA() function counts the number of cells in the referenced range that aren’t empty. UNIQUE() returns 0 when it encounters an empty cell in the source data and COUNTA() will count 0, so take care that your source data doesn’t have empty cells.

Also notice that COUNTA() is using structured referencing because the source data is in a Table object. This is important if you want both functions to be dynamic, so when entering COUNTA(), select the source data and let Excel work things out. If you manually enter the actual range, H3:H8, Excel will not update the list when you change the source data.

You might be wondering how to count the unique items in column D if you don’t have the unique list in column H. Please don’t work that hard. Even though this is possible, I recommend dropping in Excel’s UNIQUE() function somewhere and referencing it, as shown above. If necessary, hide it (although I don’t recommend hiding things as a rule because hidden things are easy to forget, making troubleshooting and modifying much more difficult).