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)

91 comments

  1. Hello! We have a excel list of 500 vet patients. Each line is one patient. We need to count the number of times words or phrases are used for a patient. E.g. The phrase "car strike" might be present in multiple columns for that one patient, but if it's mentioned at all in a line, I need to count it as "1". In other words, I need to dedup the instance of a word per line. Any advice on how to create that formula? THANK YOU!!

    1. Hello Katie!
      If I understand the question correctly, you want to count the number of unique values in a row. To do this, you can use the information from the article: How to count unique values in Excel.
      The formula might look like this:

      =SUMPRODUCT(1/COUNTIF(B1:Z1, B1:Z1&""))

      To calculate the unique text values, try this formula:

      =SUMPRODUCT(IF(ISTEXT(B1:Z1),1/COUNTIF(B1:Z1, B1:Z1),""))

  2. hi,

    i want to count the unique number of invoice per salesman, what formula should use?

  3. Hi, please help me to resolve. I have used the formula countif(G:G,G2) and pulled down to count unique values in G column.
    Now, in column A, I have another set of unique values. How do I count uniques in column G per each unique value in column A?
    Thank you,
    Olga

    1. Hi! Your formula counts how many times a value occurs in column.
      You can get list of unique values in a column by using UNIQUE function.
      If you have a list of unique values in column G and a list of unique values in column A, you can compare these columns and count matches as described in these articles: How to compare two columns in Excel for matches and differences and VLOOKUP to compare two columns in Excel for common values and missing data.
      I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

  4. How to find the no.of employees without an unwanted name?

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

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

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

      1. 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"

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

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

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

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

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

  11. 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?

  12. Hello All,

    How do I find the total sum of repeated count in a column? E.g If I have A1, A2, A3, A3, A2, B2, B2. I want the sum of repeated count to be 6

  13. Hi. How can I count unique values under a column? I'm trying to see how much customer feedbacks there are. Let's say I want to know how many people said excellent or professional.

    Column F
    1. very professional and the service provided was excellent
    2. customer service was great
    3. professional
    4. my problem got resolved
    5. the woman I spoke with was excellent and professional

      1. Thank you for your help!

  14. Hi there

    I have a dataset with an ID number in column A and a response of yes or no to 2 questions in columns B & C. I am trying to get an accurate chart of the results, where i take a unique count of ID Number for each of the 3 outcomes to the 2 questions, YES & YES = Outcome 1, YES & NO = Outcome 2 and NO & YES is outcome 3.
    My problem is that ID number 1 below has returned a different response to Q1 in the first row than it did in row 2. So my results return a value for ID number 1 in outcome 1 and also a value for outcome 3. Where an ID number answers YES in Q1 it should always trump any additional response of NO. So the unique count for Outcome 3 should not include ID Number 1.
    I can do it manually and find the duplicates and remove the incorrect records, but this is a long process as the dataset is quite large. I need to automate this process in some way. Any help is most appreciated.

    ID Number Q1 Q2
    1 YES YES
    1 NO YES
    2 YES YES
    2 YES YES
    3 NO YES

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"Yes")>0,"Yes","No") & IF(COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,"Yes")>0,"Yes","No")="YesYes",1, IF(IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"Yes")>0,"Yes","No") & IF(COUNTIFS($A$2:$A$10,A2,$C$2:$C$10,"Yes")>0,"Yes","No")="YesNo",2,3))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  15. I have a spreadsheet of census information which contains the columns "Surname", "Given Name", and "Occupation". Frequently the data in each of these columns is the same (people with the same occupation, people who have the same surname, or given name). I want to create a formula which will identify each unique value in the column, and then count the number of instances of that unique item. So for example, I want to count the number of people who have a specific occupation. Or the number of people who have a specific surname, etc. I am using this formula to find and sort the unique values in the column, and it is working fine:
    =SORT(UNIQUE(J2:J3135))
    What I'd like to add to it is " (nn)" at the end of the formula shown so that the output (for the occupation field) would look like this (where these are the unique values identified by the above formula in the J2:J3135 range):
    Apprentice (14)
    Artist (1)
    Blacksmith (4)
    Bookkeeper (2)
    Clergyman (3)
    ...etc.
    So far I am not having much luck adding the counting part of this.
    Many thanks in advance.

      1. Hello Alexander,

        I am working on a spreadsheet that is live, and ever changing. The question asked above is the most similar to what I am attempting to do. The two column titles are "Job Number" and "Company". Here is an example:

        1 Company A
        2 Company A
        3 Company B
        4 Company C
        5 Company D
        6 Company D
        7 Company E

        The UNIQUE function I am using is working correctly. Each Rep generally has 50 assigned "Job Numbers". So, In Cell C1, I enter the unique formula, and it outputs correctly:

        Company A
        Company B
        Company C
        Company D
        Company E

        What I would now like to do, is link the output in Column C, with a Total in column D. With the end result being:

        Company A | 2
        Company B | 1
        Company C | 1
        Company D | 2
        Company E | 1

        From the article that you linked above, I see how you do this. However, as jobs complete, that column is removed. This is why I would like to tie Column C and Column D. If Company B is completed and removed, This is what the data then looks like:

        Company A | 2
        Company C | REF!
        Company D | 1
        Company E | 2
        | 1

        I really hope this makes sense haha!

        2. However, as jobs complete, that ROW*** is removed. I apologize.

  16. Dear Ablebits,

    I am trying to find the number of unique agents who sold in 1 week and didn't sell in the next week. Like in the table below, Agent 3 and 4 sold in week 1 and didn't sell in week 2.

    Date Week Agent Name Sales
    1/1/21 1 Agent 1 1
    1/1/21 1 Agent 2 1
    1/1/21 1 Agent 3 1
    2/1/21 1 Agent 1 1
    2/1/21 1 Agent 2 1
    2/1/21 1 Agent 3 1
    2/1/21 1 Agent 4 1
    3/1/21 1 Agent 1 1
    3/1/21 1 Agent 2 1
    3/1/21 1 Agent 3 1
    9/1/21 2 Agent 1 1
    9/1/21 2 Agent 2 1
    9/1/21 2 Agent 5 1
    10/1/21 2 Agent 1 1
    10/1/21 2 Agent 2 1
    10/1/21 2 Agent 5 1

    Using the unique count formula, I have tried using the formula below but I have been getting the calc error -
    FILTER(Daily_sales_data!O2:O10839,(Daily_sales_data!K2:K10839=E2-1)*(Daily_sales_data!K2:K10839=(E2-2)))
    where;
    Daily_sales_data!O2:O10839 - similar to Agent Name column above
    Daily_sales_data!K2:K10839 - similar to Week column above
    E2 = Week 3, so E2-1= Week 2 and E2-2= Week 1

    Can you help me know what I am doing wrong?

    1. Hello!
      Your data is written in A2:D17 and the week number is written in E1. To calculate how many agents sold in week 1 and not sold in week 2, try the formula

      =SUM((COUNTIFS(C2:C17,UNIQUE(C2:C17),B2:B17,$E$1)>0) * (COUNTIFS(C2:C17,UNIQUE(C2:C17),B2:B17,$E$1+1)=0))

      Hope this is what you need.

  17. thank you for the answer
    it answered and it didn't at the same time, topic you gave nd here is example:
    =IFERROR(ROWS(UNIQUE(FILTER(D2:D6, (A2:A6=J5) * (B2:B6=K5) * (C2:C6=L5) * (F2:F6=M5)))), 0)
    "J5 is 1, K5 is A, L5 is 1 and M5 is done"
    here I have multiple arrays, if (zone1->area1->type1is done) it will return (3)
    I want to figure out how it will return "1"?
    as house no. 2 & 3 are not done completely.
    house no.2 only one product is done with production & delivery the other is not.

    ---A-------B------C-----------D--------------E-------------F---------------G
    zone l area l type l house no. l product l production l delivery
    --1------- A ------1-----------1------------ z1-------- done--------- done
    --1--------A ------1---------- 1------------ z2-------- done ---------done
    --1------- A ------1---------- 2------------ z1-------- done--------- done
    --1------- A ------1---------- 2------------ z2------------X--------------X
    --1------- A ------1---------- 3------------ z1-------- done--------- done
    --1------- A ------1---------- 3------------ z2-------- done -----------X

    1. Hello!
      The line "–1 ——- A ——1 ———- 2 ———— z1 ——– done ——— done” means that house 2 is done.
      It is necessary to change the list of criteria and add additional data to the table.

  18. Hello Alexander,
    hope you are well, I need help to make a formula to count unique value with multiple criteria, for example:
    count how many houses in (criteria1)zone 1,(C2)area A, (C3) type 1, done production,
    hence: if production is "done" for (1/2/3) products in a house, then it count as 1.

    when I use sum frequency with multiple criteria it gives me error "too few conditions".

    A B C D E F G
    zone l area l type l house no. l product l production l delivery
    1 A 1 1 z1 done done
    1 A 1 1 z2 done done
    1 A 1 2 z1 done done
    1 A 1 2 z2 done done
    1 A 1 3 z1 done done
    1 A 1 3 z2 done done
    1 A 2 1 z1 done done
    1 A 2 1 z2 done done
    1 A 2 1 z3 done done

    2. note: zones(1,2,3,4) are not duplicates
      but for each zone -> area(1,2,3) type(A,B,C..), house(1,2,3...), products(z1,z2) have duplicates.

  19. The =UNIQUE formula does not exist. It only gave me #NAME?
    How did you get that? or is there any other formula same results it show.

    2. Hello, how do you get a distinct list across multiple workbooks or worksheets?
      Also, how do you combine VLOOKUP and index match.
      let say I have existing list and I want to add list from another workbooks but that workbook is continuously adding up a list. been trying different formulas but it's all not working or something is missing on the formula.

  20. Hello Ablebits Team,
    I have data (text) in a column say column A with another data (also text) in column B assigned to the each data in column A. I want a formula that can count the unique occurrences of the data in column A using the data in column B as reference.

    Data
    Column A Column B
    Leo A
    Pete C
    Bright B
    June A
    Mike C
    King D
    Diana D
    Alice C
    Bright B
    Leo A
    Tom B

    Results
    A = 2
    B = 2
    C = 3
    D = 2

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)