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 Excel 365 and Excel 2021. 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)

Table of contents

179 comments

  1. Thank you for posting this. It is simple, direct, and exactly what is necessary, no need to deal with pivots or vba or anything.

  2. Muchas Gracias!!!!!!!!!!

  3. Hi Alex,

    I have below data, and want formula to get the result listed down.

    Column A Column B
    TA115 Matt
    TA115 Rani
    TA115 Rani
    TA678. Swetha
    TA678 Swetha

    I need Unique entry of Application numbers which are in Row A & respective staff assigned to -
    Example of the result -
    Column C Column D
    TA115 1
    TA678. 1

    Column C Column D Column E
    TA115 1 Matt
    TA678 1 Swetha

  4. Hi,

    I am looking to count the number of unique species by their phyla in 3 different locations. I have a sheet with three columns: site, species, and phyla. The sites are A, B, and C. Species can be grouped into phyla. I am looking for a formula that can produce the number of unique species in each phyla category per site so that I can compare species richness across sites and make graphs that are color coded by phyla.

    Trying the method described in the "Count unique values with multiple criteria" section produces an error. Seems like the error is with FILTER as that doesn't work by itself.

    Here's an example of the sheet for visualization, but it is many thousands of rows in actuality. :)

    site species phyla
    A dalli arthropoda
    A dalli arthropoda
    A chondra rhodophyta
    A caula rhodophyta
    B dalli arthropoda
    B glandula arthropoda
    B egregia ochrophyta
    B chondra rhodophyta
    C dalli arthropoda
    C egregia ochrophyta
    C silvetia ochrophyta
    C caula rhodophyta

    • Hi! I don't know what is not working for you, as you haven't written your formula. Use the recommendations from the article above and try this formula for an example:

      =COUNTA(UNIQUE(FILTER(B2:B13,(C2:C13="arthropoda")*(A2:A13="A"))))

  5. HI
    I want to count unique value with condition, for example: i have 2 coulmns i want to filter 1401 and count unique value in coulumn requisit
    Please help me.
    TNX

    Requisit year
    656100 1401
    656100 1401
    692935 1402
    338886 1398
    334001 1398
    307551 1398
    307556 1398
    313528 1398
    325855 1398
    304155 1398
    325855 1398
    648516 1401
    648516 1401
    648516 1401
    648516 1401

    • Hi! Pay attention to the following paragraph of the article above – Count unique values with criteria.
      It covers your case completely.

  6. HI,
    I have a Calculator worksheet that pulls the following from another worksheet in the workbook:
    =COUNTIF('test page Cost Centers'!F:F,TRUE).
    This is used to divide an invoice amt between certain providers by suite # (F:F)
    Here are the column headings for the Calculator worksheet.
    A B C D E F G J
    Subtotl Tax Suite Specialty SubAcct #CostCtr ProviderCode Provider Name

    The 'test page Cost Centers" worksheet has the following formula:
    =C3=Calculator!$C$6
    Here are the column headings for the Cost Center worksheet.
    A B C D E F G
    cost ctr Provider Suite Specialty (null) Match C Match D

    How do I change the COUNTIF formula to count either the suite#(F:F) and/or the Specialty(D:D) when muliple specialty are in the same suite#?

    I get a little confused with the unique values statement but think this is something needed to pull the numbers I need.

    Thank you for your time.

  7. Hi Sir, I have 3 columns of below:
    Column A Column B Column C
    20/7/2023 2023-07 Pizza Hut
    21/7/2023 2023-07 KFC
    22/8/2023 2023-08 Pizza Hut
    23/8/2023 2023-08 KFC
    24/8/2023 2023-08 Pizza Hut
    25/8/2023 2023-08 KFC

    I have used below formula to get the figure of count of KFC in 2023-08, but only get answer = 1
    =IFERROR(ROWS(UNIQUE(FILTER($C:$C,($B:$B="2023-08")*($C:$C="KFC")))),0)

    How to slot in the criteria with date (Column A) inside the formula to get answer =2 ?

    • Hi! Use column A to count unique values. Modify the formula:

      =IFERROR(ROWS(UNIQUE(FILTER($A:$A,($B:$B="2023-08")*($C:$C="KFC")))),0)

  8. Hi I would like to know how to count number of unique values that meets either of the criteria.

    Taking the above example in “ Count unique values with criteria”, I want to count the total number of winners in basketball and volleyball.

    May I know if there is a formula for such case? Thanks

    • Hi! To count the number of unique values by OR condition, use + (sum) instead of * (multiplication) in the formula. For example,

      =IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) + (B2:B10=G1)))), 0)

  9. Hi there,

    I have 4 collumns

    I have a table with values between rows 2 and 57, and between collumns C to F.

    I want to count how many times in the whole table: Collumn C contains the text "On-Call" and the Collumn D OR Collumn E OR Collumn F text on the same row contains the text "Bank Holiday".

    I can understand how I would do this if the intended logic was "and" where "or" is, but I cant figure out how to get it to work as I've written.

    I've tried:

    =COUNTIFS(C2:C57, "On-Call", OR(D2:D57="Bank Holiday",E2:E57="Bank Holiday",F2:F57="Bank Holiday"),"TRUE")

    which didn't work.

    Thanks!

      • But wouldn't that count it twice? I'm after an "or" (not exclusive) not an "and" relationship between the contents of collumns D E and F. I want it to return a value of 1 if Collumn C contains "On-Call" and at least one (but potentially up to all 3) of collumns D E and F contain "Bank Holiday"

  10. Hi,

    Is there a way to use SPILL in a FILTER formula?

    I've got a SPILL list of Unique locations.
    =SORT(UNIQUE(TablePrint[Location]))

    Secondly I want to show a dynamic list of Unique CaseID's for the locations.
    =ROWS(UNIQUE(FILTER(TablePrint[Case ID];TablePrint[Location]=AO6)))
    This works fine, but I have to fill the series manually to the same size as the first SPILL list.
    I need to get that dynamically.

    The Cell reference 'AO6' references to the first cell of the UNIQUE SPILL list of the first formula.
    If I change this reference to 'AO6#' it give's a "#N/A".

    I'm out of thoughts, so any help will be appreciated.

    Ron

  11. Hi, I have employee codes in column A, and their Gender in column B.

    1234 Female
    5678 Male
    9101 Female
    1213 Male
    1234 Female

    I want the count of females, excluding the duplicate values. For example, in the above table, the count of females should show 2 (Instead of 3) since 1234 is a duplicate value.

    • Hi! Pay attention to the following paragraph of the article above: Count unique values with criteria.
      It covers your case completely.

      • Yes, it worked!
        Please can you also help with the below
        1234 Female United Kingdom
        5678 Male Ireland
        9101 Female United Kingdom
        1213 Male Russia
        1234 Female United Kingdom

        Need the overall count of UK and Ireland employees excluding duplicates.
        The formula I used: =IFERROR(ROWS(UNIQUE(FILTER(M643:M662,AF643:AF662="United Kingdom","Ireland"))),0)

        It did not work. The above one is only pulling the count of United Kingdom. Please help

  12. i need to distinct count with multiple criteria form raw data

    Driver name City name 29-01-2023 30-01-2023 31-01-2023 01-02-2023
    Anand K Bengaluru
    Kumar K Bengaluru
    Bheemana Gouda Bengaluru
    Kakarla Sambamurthy Visakhapatnam

  13. Hi, love this and have it working mostly, but i need some assistance with the way i have my data and formula.

    For example if i have the following data, and i want to find how find how many unique models i have across Company 1 OR 2. The below should give a result of 3 unique models.

    I would use =iferror(rows(unique(filter'A:A','B:B'=1))),0) to show me how many unique values for Company 1, but how do i get it to be either company 1 or 2.

    col A Col B
    Model Company
    ABC 1
    DEF 2
    GHI 1
    ABC 2
    DEF 1

  14. Hello - I have a data tab where I have the following columns: Entity Name, Distribution Date, Distribution Amount

    Is there a way to create a new column that would label each unique distribution in chronological orders?
    Entity 1 and Entity 2 will both have a Distribution 1 label but might be on different dates

    Thanks

  15. Hi there,
    I have been using this on a MacBook and I cannot get the command to work. I have a list of companies, which I cannot share due to confedeniality, but an overview is similar to this:

    Company A - Acquisition 1 - USA
    Company A - Acquisition 2 - USA
    Company A - Acquisition 3 - France
    Company B - Acquisition 1 - UK
    Company B - Acquisition 2 - USA
    Company B - Acquisition 3 - Japan
    Company C - Acquisition 1 - USA
    Company C - Acquisition 2 - China
    Company C - Acquisition 3 - France
    Company C - Acquisition 1 - Germany
    Company C - Acquisition 2 - China
    Company C - Acquisition 3 - China

    So I want to count the number of countries that are acquired per Company but when I run the IFERROR(ROWS(UNIQUE(FILTER...) command every one comes out as zero. I ran it before and it worked fine but I cannot figure out if I am doing something wrong.

    Thanks!
    Dee

      • Hi!

        My apologies - I am relatively new to these forums and to data analyses within excel, so I am sorry for not giving the formula!

        If the company names are in column A, and the countries in column C, then I am using =IFERROR(ROWS(UNIQUE(filter($C$2:$C$17220,$A$2:$A$17220=A2))),0)

        I get the value of 0 for every row and cannot understand what I am doing wrong! Apologies for my ignorance if it is something obvious. I very very much appreciate any help you have to offer!

        Thanks,
        Dee

        • Hi!
          Your formula works for me. There may be a problem with the version of Excel. Try the formula without IFERROR and you will see where the error is.

          =ROWS(UNIQUE(FILTER($C$2:$C$17220,$A$2:$A$17220=A2)))

  16. Hi Alex,

    I have query, I have master data with different items.
    I would like to have a unique count of multiple condition. That is unique count should come depending on the other columns condition. That is countifs(a2:a40,"UTCL", d2:d50,""&"" and so on..

    Kindly help this without pivot table and array.

    Regards
    Iyer

  17. Hello,

    Client Engagement Team Lead Stage
    Akanksha Hot
    Akanksha Cold
    Akanksha Cold
    Akanksha Cold
    Akanksha Hot
    Akanksha Hot
    Akanksha Hot
    Akanksha Hot
    Akanksha Warm
    Akanksha Warm
    Akanksha Warm
    Akanksha Warm
    Neha Hot
    Neha Hot
    Neha Hot
    Neha Hot

    Formula, as below requirement. Eg

    Akanksha
    Hot - 9
    Cold - 3
    Warm - 4

  18. Hi Ablebits Team, thank you for this post, this formula have saved me a lot of time and kudos for giving me new knowledge for my work.

    I do have a question, in using the same data in your article, example on taking how many win for Basketball and under age 18 that use the below formula:

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

    how do I then extend this formula to see how many win for Basketball and Hockey under age 18 for example? I try part of the formula (B2:B10=AND("Basketball","Hockey")) in excel it doesn't work. Could you advise the best formula I can use for this issue?

    Right now on my work I have similar issue on my data and I can't be able to find the best formula to count multiple criteria under the same column as above. Thank you in advance for your help!

  19. Hi,

    I have a data of meetings which is datewise which is bifurcated week wise and month wise and then in house, video call and outdoor meetings. I want to pull the summary of this data like how many meetings are done per week unique, inhouse and outdoor.
    I could extract the summary using countifs but cant find the unique fields. can you please help

      • Date Week Month Action type Firm Name
        10-Aug-22 2 August Indoor ABC
        17-Aug-22 3 August Indoor DEF
        26-Aug-22 4 August Indoor ABC
        23-Aug-22 4 August Outdoor EFG
        30-Aug-22 5 August Outdoor EFG
        30-Aug-22 5 August Call XXX
        1-Sep-22 1 September Outdoor YYY
        1-Sep-22 1 September Outdoor KKK
        15-Sep-22 3 September Call BBB
        15-Sep-22 3 September Outdoor AAA
        17-Sep-22 3 September Call BBB
        17-Sep-22 3 September Indoor YYY
        18-Sep-22 3 September Call YYY
        20-Sep-22 4 September Outdoor XXX
        20-Sep-22 4 September Outdoor ABC

        This kindof data i have
        for count i am using below mentioned formula but i want to count unique filed
        {=COUNTIFS($D:$D,"September",$C:$C,"3",$E:$E,"Call")}
        Please suggest what criteria to add

          • I want to count unique firm name for the week for outdoor meeting or call or inhouse meeting. because in data we have met 2 or three firms multiple times but i want to count it as one. so unique count i need

  20. I am using MSO 2016 and lower version please suggest me formula for that.

  21. Hi thanks for this.. can you please help me with some modification in the formula. Is there a way to exclude a certain name in the unique range. Like with the use if your example lets say i want to exclude david in the count.

  22. Hello,
    Can someone assist with how I would show the count of unique firms per manager?
    O2:O100 has Firm #
    Q2:Q100 has Manager Name
    I can find the count of the overall unique firms, but am having trouble inputting a formula just to show that specific manager's count.
    Thank you.

    • Hello!
      Please re-check the article above since it covers your task.
      Try to use the recommendations described in this paragraph: Count unique values with criteria.

  23. Hello.. Thank u for you sharing,
    There is another way to count unique with multiple criterias, if the criterias more than 20?. It takes to long syntax if i put one by one critera like you shared..

    Thank you so much..

  24. Hi Good day,

    Column A Column B Column C
    A AA XX
    A AA XX
    A AA XX
    A AB YY
    A AB XX
    B BB XX
    B BB XX
    B BC YY
    B BC XX
    B BC ZZ

    From the sample data above, I want to count how many distinct text in column C that group by column A)and column B. The result I wish to get is like this:

    Column A Column B Column C
    A AA XX 1
    A AA XX 1
    A AA XX 1
    A AB YY 2
    A AB XX 2
    B BB XX 1
    B BB XX 1
    B BC YY 3
    B BC XX 3
    B BC ZZ 3

    For group A, AA only XX in the group show 1 for each row, For group B,BC there are YY, XX and ZZ in it so show 3 for each of them.

    I've tried using this formula =
    SUM(--(FREQUENCY(IF($A$2:A2=A2,MATCH($C$2:C2,$C$2:$C2,0)),ROW($C$2:C2)-ROW(D2)+1)>0))
    but I didn't get what I want T.T

    Hope you understand my question. Thanks in advance for helping.

      • Thank you so much for reply.

        Sorry, forgot to tell. I'm using Microsoft Excel 2010 and it don't have function UNIQUE and FILTER. Is there any functions suitable for Microsoft Excel 2010 in this situation?

        Thanks again.

  25. Hello,

    I want to count what is the number of rooms based on the date entered and cell colour. If there is no date entered, I don't want it to be counted. The rooms is arranged in multiple rows and column.

    As example, the total number of blue cells is 458. In that 458, 110 cells have date meanwhile the rest is empty. So, how do I write formula for this as I am dealing with multiple colours as well.

  26. Hi, I'm trying to count unique values with multiple criteria but nothing seems to fit what I need.

    I have a sheet with data and a sheet with results within the same book.
    In a cell on the results sheet I am trying to count the number of unique items (job number) on the data sheet that match two sets of criteria, because the same criteria could have multiple lines under the same job number (for different months).

    Eg on the data sheet:
    Job# Month Customer Status
    123 Jan Joe's jugs Closed
    123 Feb Joe's jugs Closed
    124 Jan Joe's lamps Open
    125 Feb Jim's tables Closed
    126 Feb Joe's pots Closed

    I'm looking for the number of jobs on any account that belongs to Joe (so using a wildcard that Sumproduct can't handle) that are closed, but with unique job numbers (due to the spillover of job into other months). ie I'm trying to get a formula to give me a result of 2 [job 123 and 126], but can't find anything that works. Closest I've come is COUNTIFS, but I can't remove the duplicate job numbers so it will give me 3 [job 123 twice plus 126]. If I try to incorporate UNIQUE into that I get a SPILL error.

    • Hi!
      Please re-check the article above since it covers your task.
      Here is the formula for your task:

      =COUNT(UNIQUE(FILTER(A2:A6,(C2:C6="Joe’s")*(E2:E6="Closed"))))

  27. Hello -
    I'm trying to count the numbers of new projects in a certain type of work. Within a project might be multiple jobs, so i'm trying to count the unique project number by job type.

    I'm using the IFERROR(ROWS(UNIQUE(range, criteria_range=criteria))), 0) formula, but it is not pulling the expected results. It also changes the results if the table is filtered differently.

    Is there a way to send you my excel sheet for more info?
    =IFERROR(ROWS(UNIQUE(FILTER("Range of Project #'s","Range of job Types"="Job Type look for count of"))),0)

    Thank you!
    Chad

    • Hi!
      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 shorten your tables to 10-20 rows/columns and include the link to your blog comment.
      We'll look into your task and try to help.

  28. Hi, As per shared expample for Count unique values with criteria, I want to find the count of multiple sport for winners. Means if Andrew having intrest in 2 sports (like basketball- 2 and Hockey-1). I want answer number 2.

  29. in sheet1 at A:A i have data as medicine names, At sheet 2 from C:C i want to create autocomplete drop down list from the data , which should complete by just 2-3 letters, based on data at sheet1

    please help

    • Hello!
      Data Validation doesn't have an AutoComplete feature. You need to use Combo Box. To insert it, use the menu Developer > Insert > Combo Box (ActiveX Control).

  30. Hello,

    I am trying to determine the amount of "New Customers" we received in a given FY. This data has opportunities that are split between reps, giving me an extra "New Customer" since it has a different opportunity ID. I am trying to use countif in a column by "New Customer" while using Sumproduct on another column (account name) to not count any "New Customers" that have the same account name.

    • Hi!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

  31. I need help please. I want to count the active months of production from a specific date, including the zeros.... how do I do this if all the dates are different?

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