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

IFERROR(ROWS(UNIQUE(range, criteria_range=criteria))), 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).
Counting unique values with 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:

IFERROR(ROWS(UNIQUE(range, (criteria_range1=criteria1) * (criteria_range2=criteria2)))), 0)

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

Practice workbook for download

Count unique values formula examples (.xlsx file)

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

  4. Jeffry says:

    Good day to Ablebits Team!

    i have a column of dates with ten entries (Random future dates) for each ten different material, i wanted to have a notification that would tell me how many of those materials will expire 30 days before the dates mentioned in their respective columns. any help would do.

  5. Alfred Peverelli says:

    Tab Unique values multiple criteria, when entering a value of 10 in F2, then E5 displays a #CALC! error message as expected, but F5 displays a value of 1 as COUNTA also calculates the cell which contains a string. Using such a formula may render incorrect results, which aren't very obvious for users, without thorough checking. Unfortunately cannot use the COUNTBLANK formula to add to the existing formula and subtract records when containing a string to come to the correct result. Any other workaround?

    • Hello Alfred!
      Thank you for pointing out this issue! We will check all the formulas in this tutorial for "non-matched" criteria and fix the erroneous ones.

      In the meantime, you can use these formulas:
      E5
      =IFERROR(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10<F2))),"")

      F5
      =SUM(--NOT(ISERROR(UNIQUE(FILTER(A2:A10,(B2:B10=F1)*(C2:C10<F2))))))

      I hope it’ll be helpful.

    • Hi Alfred,

      Thank you very much for catching this error! And my apologies for not testing the formula when the criteria are not met. In addition to Alexander's response, I can suggest the following solution:

      You can use the ROWS function to count unique entries (unlike COUNTA, it does not count error values) and IFERROR to trap all kinds of errors and replace them with 0. So, the formula in F5 would go as follows:

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

      We have updated this and a few other formulas in this tutorial. Thank you for helping me make this post a little better :)

  6. Petrine says:

    Hi,
    Thank you for this! Can you help me how to implement an extra condition? In your example it would correspond to adding the criteria of Age greater then 16 but below 19. When adding an extra condition it just counts 0 which is not the right case. See below implementation:
    =IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C1016))), 0)

  7. Anthony Emmanuel says:

    some commonly use function in excel explan with example

  8. Jack says:

    Hello, I'm trying to count unique individuals whose work location(s) are Ashburn (Column E) which could be depicted as 123 main st., Ashburn VA. An individual (Column A) can work at multiple Ashburn locations but I am only interested in counting them once. As an example, there are nine people working in Ashburn locations, but there are only three unique individuals working in Ashburn. I've looked at tons of examples but I simply can't get any of the formulas to work. Any ideas?

  9. Rakesh says:

    is there any way to count the unique value based on a text string in another column ?
    In the above example, count the unique values , if the other column contain "ball" ( basketball /Volleyball )

  10. Ani says:

    Hi,
    I have a sheet where there are multiple rows for a given text in a column, i need to pull data if there is only one unique row , if there is more than 1 row then need to display the number of rows

  11. Simone says:

    Hi,
    Your formulas an explanantions are very helpful thanks! I am struggling with getting the correct results and I'm not sure where the issue is. This is the formula I'm using, and it's giving me a reulst of 0 for all...
    =IFERROR(ROWS(UNIQUE(FILTER(Table_SDCdata[Site], (Table_SDCdata[Format]=[@Format]) * (Table_SDCdata[Region]=[@Region])))), 0)

    Just for the data size example: Main table I'm counting and filtering has 37 columns and 140 500 rows.
    So it's suppose to count the unique 'Site' values in the Main table (becuase there are many duplicates) if the 'Region' and 'Format' matches that in the current table.
    I used "=COUNTA(UNIQUE(FILTER(" before but it was giving a value of "1" for everything if I use it on big data sets, for smaller ones it works. From what I read online "COUNTA" doesn't work well with big data sets, so I have to find an alternative. Any reason as to why it's not working? Will it also be because of the data size?

    • Hello!
      Unfortunately, without seeing your data it is impossible to give you advice.

      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please include the link to your blog comment.

      We'll look into your task and try to help.

      • Simone says:

        Hi,

        Thank you in advance for the help: I've emailed the sample file as requested.

        What I'm trying to achieve is the following: Count all the unique 'Site' codes in the Master table, if the 'Region' and 'Format' matches that from the template table. There are many duplicates in the Masterfile for each site that matches the region and format, therefore I only want to count unique once. A small example (in case anyone else reads the post for future):
        MASTER TEMPLATE
        Format Region Site Format Region Count
        Corp EC EC1 Corp EC 2 (This would be EC1 and EC2)
        Corp WC WC1 Fran EC 1
        Fran EC EF1 Exp EC 1
        Exp EC EE1 Fran EC 1
        Corp EC EC1 Fran EC 1
        Fran WC WF1 Corp EC 2
        Corp EC EC2

        • Hello!
          Your formula is working correctly. However, Excel does not work correctly with so much data. If you replace your formula with

          =IFERROR(ROWS(UNIQUE(FILTER($B$2:$B$1000, ($A$2:$A$1000=A2) * ($D$2:$D$1000=D2)))), 0)

          then it counts the number of unique values correctly.

          An alternative option for counting unique values by 2 conditions is

          =SUMPRODUCT((($A$2:$A$10000=A2) * ($D$2:$D$10000=D2)) / COUNTIFS($A$2:$A$10000, $A$2:$A$10000, $D$2:$D$10000, $D$2:$D$10000, $B$2:$B$10000, $B$2:$B$10000))

          This formula also works correctly, but if you increase the range to 100,000 rows, it returns 0.

  12. Beatriz Amador says:

    Why if I put this formula
    =IFERROR(ROWS(UNIQUE(FILTER(C2:C210,J2:J210=J2))), 0), it get me some value
    if I increase the range from 210 to for example 400... It gives me 0 as result?
    thanks

  13. Yogesh Suvarna says:

    Hi ,
    I need help with count distinct in the below table-
    Variant Subcat CustomerCode CustomerName ProductName Jan Feb
    CREAM CREAM 6079 AL AHLI S/M CHOCOLATE CREAM 11105026 21 5
    CREAM CREAM 6079 AL AHLI S/M MANGO CREAM 11105029 21
    CREAM CREAM 6079 AL AHLI S/M ORANGE CREAM 11105028 21 4
    CREAM CREAM 99 AL DHAFRA SM ORANGE CREAM 11105028 21
    CREAM PROMO 99 AL DHAFRA SM CREAM BISC 11205001 269 30
    CREAM CREAM 7935 AL DOURIZ CHOCOLATE CREAM 11105026 21
    CREAM CREAM 7935 AL DOURIZ MANGO CREAM 11105029 22 4
    CREAM CREAM 4900 AL MADINA HYPERMARKET MANGO CREAM 11105029 21
    CREAM CREAM 4900 AL MADINA HYPERMARKET ORANGE CREAM 11105028 21

    CREAM CREAM No of customers who purchased Variant Cream 4 3
    CREAM PROMO 1 1

    I need to count the no of customers who purchased cream under each month , irrespective of the flavor.
    In the table , no of people who purchased cream was Jan - 4 , feb -3
    Similarly people who purchased cream promo was Jan =1 and Feb =1.
    Countif function counts all the product names as well .

  14. Prof. Dr. Elias M. Choueiri says:

    As usual, your tutorial gets directly to the point in a friendly manner. I encourage everyone to follow your tutorials 'cause they present the steps needed to accomplish a certain task in Excel in an easy way.

    Keep up the great work, and many thanks for the professional work you spread around with your tutorials!

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)
Ultimate Suite for Microsoft Excel
Merge two tables
Combine Sheets
Merge Duplicates
Consolidate Sheets
Copy Sheets
Merge Cells
Vlookup Wizard