How to count unique values in Excel an easy way

Count unique values in column

The easiest way to count unique values in a column is to use the UNIQUE function together with the COUNTA function:

The formula works with this simple logic: UNIQUE returns an array of unique entries, and COUNTA counts all the elements of the array.

As an example, let's count unique names in the range B2:B10:

=COUNTA(UNIQUE(B2:B10))

The formula tells us that there are 5 different names in the winners list:

Count unique values that occur just once

In the previous example, we counted all the different (distinct) entries in a column. This time, we want to know the number of unique records that occur only once. To have it done, build your formula in this way:

To get a list of one-time occurrences, set the 3rd argument of UNIQUE to TRUE:

UNIQUE(B2:B10,,TRUE))

To count the unique one-time occurrences, nest UNIQUE in the ROW function:

ROWS(UNIQUE(B2:B10,,TRUE))

Please note that COUNTA won't work in this case because it counts all non-blank cells, including error values. So, if no results are found, UNIQUE would return an error, and COUNTA would count it as 1, which is wrong!

To handle possible errors, wrap the IFERROR function around your formula and instruct it to output 0 if any error occurs:

=IFERROR(ROWS(UNIQUE(B2:B10,,TRUE)), 0)

As the result, you get a count based on the database concept of unique:

Count unique rows in Excel

Now that you know how to count unique cells in a column, any idea on how to find the number of unique rows?

Here's the solution:

The trick is to "feed" the entire range to UNIQUE so that it finds the unique combinations of values in multiple columns. After that, you simply enclose the formula in the ROWS function to calculate the number of rows.

For example, to count the unique rows in the range A2:C10, we use this formula:

=ROWS(UNIQUE(A2:C10))

Count unique entries ignoring blank cells

To count unique values in Excel ignoring blanks, employ the FILTER function to filter out empty cells, and then warp it in the already familiar COUNTA UNIQUE formula:

With the source data in B2:B11, the formula takes this form:

=COUNTA(UNIQUE(FILTER(B2:B11, B2:B11<>"")))

The screenshot below shows the result:

Count unique values with criteria

To extract unique values based on certain criteria, you again use the UNIQUE and FILTER functions together as explained in this example. And then, you use the ROWS function to count unique entries and IFERROR to trap all kinds of errors and replace them with 0:

For example, to find how many different winners there are in a specific sport, use this formula:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10,B2:B10=E1))), 0)

Where A2:A10 is a range to search for unique names (range), B2:B10 are the sports in which the winners compete (criteria_range), and E1 is the sport of interest (criteria).

Count unique values with multiple criteria

The formula for counting unique values based on multiple criteria is pretty much similar to the above example, though the criteria are constructed a bit differently:

Those who are curious to know the inner mechanics, can find the explanation of the formula's logic here: Find unique values based on multiple criteria.

In this example, we are going to find out how many different winners there are in a specific sport in F1 (criteria 1) and under the age in F2 (criteria 2). For this, we are using this formula:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10<F2)))), 0)

Where A2:B10 is the list of names (range), C2:C10 are sports (criteria_range 1) and D2:D10 are ages (criteria_range 2).

Practice workbook for download

Count unique values formula examples (.xlsx file)