How to count unique values in Excel an easy way

The tutorial looks at how to leverage the new dynamic array functions to count unique values in Excel: formula to count unique entries in a column, with multiple criteria, ignoring blanks, and more.

A couple of years ago, we discussed various ways to count unique and distinct values in Excel. But like any other software program, Microsoft Excel continuously evolves, and new features appear with almost every release. Today, we will look at how counting unique values in Excel can be done with the recently introduced dynamic array functions. If you have not used any of these functions yet, you will be amazed to see how much simpler the formulas become in terms of building and convenience to use.

Note. All the formulas discussed in this tutorial rely on the UNIQUE function, which is only available in the latest version of Excel 365. If you are using Excel 2019, Excel 2016 or earlier, please check out this article for solutions.

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:

COUNTA(UNIQUE(range))

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:
Excel formula to count unique values in a column

Tip. In this example, we count unique text values, but you can use this formula for other data types too including numbers, dates, times, etc.

Count unique values that occur just once

In the previous example, we counted all the different (distinct) entries in the column. If you are interested to know the number of unique records that occur only once, then set the 3rd argument of UNIQUE to TRUE:

=COUNTA(UNIQUE(B2:B10,,TRUE))

As the result, you will get a count based on the database concept of unique:
Counting unique values that occur only once

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:

ROWS(UNIQUE(range))

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))

Excel formula to count unique rows

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:

COUNTA(UNIQUE(FILTER(range, range<>"")))

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:
Counting unique entries ignoring blank cells

Count unique values with criteria

To extract unique values based on certain criteria, you again use the UNIQUE and FILTER functions together as explained here and enclose that formula in COUNTA to get the unique count:

COUNTA(UNIQUE(FILTER(range, criteria_range=criteria)))

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

=COUNTA(UNIQUE(FILTER(A2:A10, B2:B10=E1)))

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).

Counting unique values with criteria

Count unique values with multiple criteria

The formula for counting unique values based on multiple criteria is very much alike the above example, except that you construct the criteria a bit differently:

COUNTA(UNIQUE(FILTER(range, (criteria_range1=criteria1) * (criteria_range2=criteria2))))

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

In the meantime, 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:

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

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

That's how to count unique values in Excel with the new dynamic array functions. I am sure you appreciate how much simpler all the solutions become. Anyway, thank you for reading and hope to see you on our blog next week!

Available downloads

Count unique values formula examples

You may also be interested in:

5 Responses to "How to count unique values in Excel an easy way"

  1. Sunil Pinto says:

    Dear Ablebits...
    Please help me to fix this task
    I have data in “A” column and input in “B” column and required result is in ” C” column.
    I already found the result is in “E” Column. But this result needs to fix the corresponding raw of the A Column.

    Data1 Data2 Required Result Formula
    1 2 1 1
    5 5 7
    7 9 7 11
    9 16 11
    9 25
    11 11 25
    11 11 33
    16 35
    16 58
    25 25 60

    Array Formula in E2 Column is :=IFERROR(IFERROR(INDEX($A$2:$A$21, SMALL(IF(COUNTIF($B$2:$B$21,$A$2:$A$21)=0, MATCH(ROW($A$2:$A$21),ROW($A$2:$A$21)), ""), ROWS($E$2:E2))), INDEX($B$2:$B$21, SMALL(IF(COUNTIF($A$2:$A$21,$B$2:$B$21)=0, MATCH(ROW($B$2:$B$21),ROW($B$2:$B$21)), ""), ROWS($E$2:E2)-SUM((COUNTIF($B$2:$B$21, $E$2:$E2)=0)+0))))," ")

  2. Shashikumar H says:

    Dark Chocolate 25gm box 12 pcs
    Dark Chocolate 20gm*24 box
    White Chocolate 15gm
    White Chocolate 25gm*24
    Biscuits W/Marshmallow300gm
    Chocolate 40gm
    Can some plz help to extract the numbers before "gm", for example : 25,20,15,25,300,40

  3. Ben says:

    Dear experts,
    I saw an article on getting distinct values. While i understand the array distinct formula, i am not able to understand the regular distinct formula.
    MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0))
    not sure why there is a index function as it always return a value of 1 instead of an array not matter what is the range of column "B". Example MATCH(0, INDEX(COUNTIF($B$1:B3, $A$2:$A$10), 0, 0), 0))
    However, I tested this formula, it works. Just do not understand the rationale behind how this works.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard