Count the number of unique values in a range [Quick Tip]

Posted on August 6th, 2009 in Excel Howtos , Learn Excel - 59 comments

Here is an excel formula quick tip that can come handy when you need to count the number of unique values in a range of cells.

Excel Formula to Count Number of Unique Values

count-unique-values-from-rangeAssuming we have a list of values in the range:B5:B15 and we want to know how many unique values are there,

you can write the oh so wonderful sumproduct formula like this:

SUMPRODUCT(1/COUNTIF(B5:B15,B5:B15)).

How this formula works?

The portion COUNTIF(B5:B15,B5:B15) of the formula returns the individual item counts. So in our example range, the values would be: 5,1,2,5,1,1,1,2,5,5,5. When you add up the reciprocals of these values, you will get the count of unique values in the range.

Why so?
I am leaving that to your imagination.

More unique ways to work with duplicates in your data

My obsession with unique values in excel is known to most of you. It is one topic on which I have posted alot. Here is a list of most useful techniques to work with duplicate values in your data.

Additional Resources:

If you are an Excel Formula Stud …

Take part in our twitter formula contest. All you need to do is send a tweet with a formula tip to @r1c1. We have prizes worth $230 for grabs. More details here.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

59 Responses to “Count the number of unique values in a range [Quick Tip]”

  1. Chris says:

    very clever! thx

  2. spudinsane says:

    how come it shows 6 “unique” values when it should actually be 5? i see that bart is referenced twice. put any other name aside from bart or homer in that range and you will see the difference of the “included” range being miscalculated. something is not being done right here and im too much of a novice to figure out in excel how to calc it out properly. i can in my mind but cannot in excel lol!

  3. spudinsane says:

    basically it looks like the “unique” customers are only bart & homer!

  4. Chandoo says:

    @Chris.. You are welcome :)

    @Spudinsane: I think it is in mind.. :P I can count Homer, Marge, Bart, Lisa, Skinner and Smithers.

  5. Chandoo says:

    @Spudinsane: no.. they are called as repeated customers. Try this: http://www.google.com/search?q=define%3A+unique

  6. spudinsane says:

    thanks for letting us know that you meant “repeated customers” instead of “unique customers” because if it were repeated customers, that would only include Bart or Homer, regardless. ; )

  7. Chandoo says:

    @Spudinsane.. I am not sure you understand the concept of unique. Let me know if you have difficulty understanding what the above formula is doing or why you think the count should be different.

  8. spudinsane says:

    unique to me, until you clarified it from your POV was that you counted each of the “individual names” ( Homer, Marge, Bart, Lisa, Skinner and Smithers ) as being “repeated customers” or “unique” from your perspective. it does not mean that the formula was incorrect because that does add up to “6″. what threw me off was your reference to “6 unique customers of the day”…..and I counted only “5″ Homers while thinking that their should be 6 of them, and that was what made it “unique” from my point of view, the idea that you singled out how many Homer’s were in that list, which, in turn made me automatically think that the formula “could” have been wrong. as it is i figured that out by adding my own name to the range and it came up as “7″, which proves that the formula was correct as you alluded to.

  9. JP says:

    Works great but only in small spreadsheets… when i try the formula to get uniques on 84k rows of 10 digit phone numbers, the PC froze up on multiple attempts. =(
    Any Suggestions?

    • pmsocho says:

      Hello JP,

      It’s been a long time but maybe it will help someone…

      This formula:
      =SUM(1/COUNTIF(H8:H25001,H8:H25001))
      is easy and cool but a bit inefficient for big data.
      Try such construction instead:
      =SUMPRODUCT(–(FREQUENCY(MATCH(H8:H25001,H8:H25001,0),MATCH(H8:H25001,H8:H25001,0))>0))

      The SUMPRODUCT handles array operations so there is no need for Ctrl+Shift+Enter.

      When I timed both formulas for 25000 records, the time of execution for the first one was 00:01:14 and for the second one it was 00:00:09.

  10. Chandoo says:

    @JP… you are pushing excel calculation limits now :P. It might be better to write a VBA Macro to count uniques over such a large range. Sumproduct is internally an array formula and usually they are in-efficient compared to the normal formulas. It would be far easier for your computer to loop through numbers in one go and increment the no_of_uniques counter for each new phone number it sees.

  11. JP says:

    @chandoo Thanks, but I just don’t know how to do Macros yet. I was thinking a before i manage to get something out of knowing my uniques i should look for ways to better manage such huge loads of data like access/microsoft query for example=P

  12. suman says:

    There are two ranges a1:a50 and b1:b50 with different numeriacal values and i need to know that whether there is any duplicate or not. So what might be the solution ?

  13. Ari says:

    This is a great post. Is there, however, a way to modify the formula above to replace COUNTIF with COUNTIFS, given that i’d like to count unique values under a given set of criteria?

    Thanks in advance.

  14. Jo says:

    Wonderful…have been trying to work this one out for ages. Thanks very much!

  15. Binod says:

    Thanks for the great post. However, my requirement is to count the unique values excluding the hidden rows. Is there any solution?

  16. Lucy Ayala says:

    I have column of numbers and I would like count the individual digits separately e.g. 0=89
    1=103 etc. what formula can I use to make this possible.?

    column example—- my excel column has over 400 3digit and four digit numbers
    108
    987
    389
    310
    910
    986
    231

    • Chandoo says:

      @Lucy… interesting problem. Assuming your numbers are in the range A1:A400 and you have 0,1,2,3…9 in cells B1:B10, in C1 write,

      =SUM(LEN($A$1:$A$400)-LEN(SUBSTITUTE($A$1:$A$400,B1,””))) and press CTRL+SHIFT+Enter
      This gives you the number of times 0 has occurred in the list (since B1 has 0 in it)
      Now, drag it down to get totals for 1 thru 9.

  17. Lucy Ayala says:

    CHANDOO,

    THANK YOU FOR YOUR QUICK RESPONSE. I TRIED YOUR SUGGESTION AND GOT AN ERROR 508– PERHAPS YOU KNOW WHAT I’M DOING WRONG— THANKS AGAIN FOR YOUR ASSISTANCE!

    LUCY

  18. Hui... says:

    @Lucy
    Retype the formula, don’t cut and paste
    The ” ‘s above look like ” but aren’t
    make sure you Pres Ctrl Shift Enter and not just Enter
    .
    I’d also use a slight variation to Chandoo’s
    =SUM(LEN($A$1:$A$400)-LEN(SUBSTITUTE($A$1:$A$400,ROW()-1,"")))
    Ctrl Shift Enter

  19. Jesse says:

    I’ve got a similar issue but needs an extra step. I want to find the unique values in a given period. For example Column A has 73 periods and Column B has 14 unique values (1299 rows total). So period 1 only has 2 unique values. Example
    1 1
    1 1
    1 2
    1 2
    2 3
    2 4
    2 9

    Thanks in advance

  20. A B Sunilkumar says:

    Hi Chandoo:
    Require one help from you, I have got few values, say in cell a1 to a50. I have got one value say 100 in cell b1. now i want to know how many combination of numbers from cell a1:a50 would give me the number 100 as given in cellb1. apart from knowing how many combinations would give this number 100, i also want to know which are these combinations. Is it possible in excel?
    Thanks in advance.
    Best Regards
    ABS

  21. B J says:

    Hi,
    I’m trying to use the sumproduct formula as mentioned above to count the number of unique values in my data (which is in columns A through K) and the data that I’m trying to count is in column G:G, but this is not working for me – I get a value of #DIV/0 error. The data in column G:G is seven character numeric and is on a different worksheet within the same workbook. There are about 50 rows in total and when I manually count the data, there are about 14 pairs of duplicates in G:G.

    Any ideas how to fix this?

  22. ScooterBugg says:

    I tested this at home and it worked like a champ.
    I try to use it at work and I get #DIV/0 error just as B J just mentioned.
    My range is only 1x50ish, each cell is 3 alphanumeric.
    Help!?!?
    Actually, I’m looking for simple formula to turn on a “YOU HAVE DUPES” in a top row cell, a la:
    =IF(SUMPRODUCT(1/COUNTIF(MYRANGE,MYRANGE)) = COUNTA(MYRANGE), ” “, “YOU HAVE DUPES”)

    • Hui... says:

      @ScooterBug
      .
      You will get a #DIV/0 when your Range MYRANGE is not fully populated
      That is if MYRANGE is from A1:A20 make sure each cell in that range has an entry.

  23. BigRig says:

    I want to keep a running total as I enter new rows of data, so is there a way to filter out the blank entries in the formula?

  24. Ganesh says:

    Beautiful formula. Very guilty to have to search for it instead of thinking through it myself!

  25. Tony the EE says:

    Is there a way to combine this with filters? Subtotal really should have a sumproduct function. Also, this fails on blank values (hence the filter)

  26. chinmay says:

    Hi i have a question,
     
    now in A column i put date with month and year and in other column i put saleperson name….now if i want to find that in perticular month how much time his name comes in b column…so what i use

    • Hui... says:

      @Chinmay

      It will be something like:

      =SUMPRODUCT((A2:A400=DATE(2012,1,1))*(B2:B400=”John”))

      or

      =COUNTIFS(A2:A400,DATE(2012,1,1),B2:B400,”John”)

  27. PoolBoy says:

    Chandoo – this formula is brilliant!  I’ve modified it a bit to allow changes in the range that it’s checking.  In this case, the range I’m looking at to count unique values is in Column E and the last cell in the range is the value in cell M2.  Here’s the modified formula: =SUMPRODUCT(1/COUNTIF(INDIRECT(“E2:E”&M2),INDIRECT(“E2:E”&M2)&””))  When I drop this into my VBA code as this:  ActiveCell.formula = “=SUMPRODUCT(1/COUNTIF(INDIRECT(“E2:E”&M2),INDIRECT(“E2:E”&M2)&””))” the complier chokes.  Do you have any advice?

    • Jimmy_Mac says:

      PoolBoy, you have to be careful of quotation marks when passing a string to the .formula.  It treats the quotes around E2:E as a stop quote and start quote, and therefore doesn’t understand E2:E as part of the string itself.  This is when you need to get into double quotes, triple quotes and quadruple quotes.  It can be a bit of a headache with longer formulas but I’d recommend you google quotation marks in VBA strings.
      In the meantime, try this:
      ActiveCell.Formula = “=SUMPRODUCT(1/COUNTIF(INDIRECT(“”e2:e”” & R[-6]C[6]), INDIRECT(“”e2:e”” & R[-6]C[6]) & “”””))”

      • Jimmy_Mac says:

        Sorry forgot to convert that.  change R[-6]C[6] to M2.
        (I cheated and used record macro because I didn’t want to deal with the quotes myself since I haven’t done so in a while :)

  28. [...] answered with a neat Sumproduct() based formula and quoting a post that Chandoo had written at Chandoo.org answering the question in [...]

  29. bill says:

    great post. i think the request for more insight into using COUNTIFS in the count unique values formula got lost in the conversation. i am interested in counting unique values/text in a filtered set (based on criteria from other columns). thanks for this great, great blog.

  30. KEM says:

    Great formula (still cant understand how it works.)

    I used it in a range , but I need it to show only the results of the visible items once I filter the range.

    Can this be done? 

    • Abby Wei says:

      simple math,

      if 5 appears 5 times,  reciprocals of 5 means, letting it breaks 1 into 5 parts
      if  1 appears 4 times, reciprocals of 4 means, letting it breaks 1 into 4 parts
      and so on 

      Then use sumproduct is to collect all the broken parts together

      by the way, following the same concept

      {=sum(1/ COUNTIF(B5:B15,B5:B15))} works the same as  
      SUMPRODUCT(1/COUNTIF(B5:B15,B5:B15))  

  31. Ian L says:

    A use of sumproduct that can handle blank cells is as follows (got this from RagDyeR user at

    =SUMPRODUCT((A1:A30000<>””)/COUNTIF(A1:A30000,A1:A30000&””))

    Isn’t an array formula and doesn’t need ctrl-shift-enter. Sums up the fractions of the occurences of the unique entries – which gives the total number of unique entries… very clever I thought. See RagDyeR’s entries at: http://www.excelforum.com/excel-formulas-and-functions/365877-count-distinct-values.html

  32. Dada says:

    I have a file containing more than the following data in column A:

    column A
    1
    2
    4
    1

    I want to identify the unique values as well as the count for each unique value. For the above data the output should be:

    column A column B
    1 2
    2 1
    4 1

    Please how do you write the formula column A and column B?

  33. infinitedrifter says:

    I have a table of call logs w/ the employee extensions (customer time[extensions]) in one column and dates (customer_time[date int]) in another (converted to an integer so the time data is scrubbed). I have a start date (wtd_start) and end date (wtd_end) and need to calculate the number of days each extension was logged in the given period, which will equal the number of days worked for that employee. The extensions are duplicated multiple times in the list as are the dates, so I need to calculate:
    the number of unique dates for each extension withing a given date period.
    The table is already about 5500 lines for about 4 months (in a large workbook), so I’m trying to make the formula as low processor-intensive as I can.
    So far I have
    =SUM(IF(FREQUENCY(IF((customer_time[Date Int]“”)*(customer_time[Date Int]>=Wtd_Start)*(customer_time[Date Int]<=Wtd_End),customer_time[Date Int],""),IF((customer_time[Date Int]“”)*(customer_time[Date Int]>=Wtd_Start)*(customer_time[Date Int]0,1,0))

    which does NOT take into account the individual employee extensions, it just calculates the number of unique dates that appear in the table within the given range.

    Any suggestions on incorporating the extension calculation and/or speeding up the formula? Or even moving the process to VBA?
    I’ve tried SUMPRODUCT but am admittedly not as familiar w/ it plus it seems slow. Or is there a way to do it with COUNTIF?

    Thanks!

  34. pravesh says:

    how is possible that count the number & text without duplicate values but count the duplicate value in one time for example : A011,A012 A011,A013,A014 = (ans is 5)

  35. Stephen says:

    Could you help me with (1) the Count the number of unique Divisions where ALL products have been delivered. (2) The Count the number of unique Divisions where NOT ALL products have been delivered

    Three columns of data, listed below.

    Division Product Delivered Date
    AN9 CPPR.T014.AN9.QD.R00002.F090101.T130926.F001 28-Sep
    GH3 CPPR.T014.GH3.QD.R00002.F090101.T130926.F001 29-Sep
    L4S CPPR.T014.L4S.QD.R00002.F090101.T130926.F001 30-Sep
    L4S CPPR.T014.L4S.QD.R00002.F090101.T130926.F002 29-Sep
    L4S CPPR.T014.L4S.QD.R00002.F090101.T130926.F003
    L53 CPPR.T014.L53.QD.R00002.F090101.T130926.F001
    L7W CPPR.T014.L7W.QD.R00002.F090101.T130926.F001
    NHP CPPR.T014.NHP.QD.R00002.F090101.T130926.F001
    L3N CPPR.T014.L3N.QD.R00002.F090101.T130926.F001 24-Sep
    WH3 CPPR.T014.WH3.QD.R00002.F090101.T130926.F001
    L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F001 25-Sep
    L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F002 25-Sep
    L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F003 26-Sep
    L4Y CPPR.T014.L4Y.QD.R00002.F090101.T130926.F004 27-Sep

  36. Tara says:

    Hi Guys please help.

    I have a row of 20 cells. In this row the numbers in those cells can change up to 5 or 6 times. is there a way that i can count how many times the number has changed and what it has changed to.
    E.G ROW 885 885 885 2184 2184 562 885 885 885 562 562 885 885 885 914 885 885 713 885 885

    I need an answer that will say 885 – 13.
    2184 – 2
    562 – 3
    914 – 1
    713 – 1

    Thank in advance

  37. Aaron says:

    I have over 2000 rows that each have 3 letter codes.
    I need to count the number of unique duplicates. so for this example it should be: 3 unique duplicates even though there are 7 total duplicate codes

    How do I do this?

    WAT
    WAT
    WAT
    ALN
    ALN
    AIU
    JAT
    IPO
    YEG
    ETC
    ETC

  38. Philippe says:

    Great tip !!!!

    But how to count the following:
    I have a column A with repeated dates and a column B with repeated names. How to count the number of unique values of Date in column A for a specified name of column B ?

    Thanks for help.

    Philippe

  39. Sokchea says:

    How can we develope a formula by using VBA code instead of this formula =SUMPRODUCT((A1:A30000””)/COUNTIF(A1:A30000,A1:A30000&””))

  40. noob says:

    I have;
    A B C
    Project ID`s YEAR Amount
    1 2010 20$
    2 2010 50$
    3 2010 80$
    4 2009 90$
    4 2009 25$
    4 2011 45$
    I cannot have same project ID (4) with two same years (2009). How can I highlight them or identify them in order to erase them. I have 4000 entries. Please help ! Than you

  41. Masood Ahmad says:

    Hi,

    I have a situation and needs your quick reply. Can anyone help me sort out my problem:

    On my Excel dashboard, the data is calculated from the DATA sheet. Among the many formulas, one is listed below:

    =SUMPRODUCT((Data!$A$6:$A$2500>=B$6)*(Data!$A$6:$A$2500<C$6)*(Data!$E$6:$E$2500="Simple"))

    where: Column A contains Date, Column E contains the category. This formula calculates the number of Simple jobs came in between two dates.

    As this formula counts the number of cells in E meeting the criteria. It doesn't even look at the job numbers which are in Column B, whether they are duplicate, unique or combination of both.

    Now I would like to add one more condition to it. That it should count the number of jobs 'Column B', using the same conditions and not counting the duplicate entries. Moreover, only the first duplicate needs to be counted:

    For example
    Column B:
    1001
    1002
    1003
    1004
    1005
    1006
    1006
    1004
    1001
    1001

    In the above example, the number of jobs (whether duplicate or unique) is 6.

    • Masood Ahmad says:

      Sorry to mention one thing:

      In the above example, the number of jobs (whether duplicate or unique) is 6.

      i.e. the formula will look for the Simple jobs falling between two dates excluding the duplicate entries. Mind it that duplicate entries means second and so on… The duplicate jobs will be calculated only once meeting the criteria.

  42. Masood Ahmad says:

    For more information about my data and the required result:

    The complete formula is given below with other details:

    Data Sheet:
    Column A=Dates (For example 1/5/2014, 2/5/2014, 3/5/2014….. 30/5/2014)
    Column B= Job numbers (For example 4584160, 3926039, 3600528, 4524528, 4524528, 3926039, 3926039, 4512014 etc)
    Column E=Complexity of the job received (for example “Simple” or “Complex”)

    Dashboard sheet:
    B6=1/5/2014 (Beginning Date)
    C6=8/5/2014 (End Date)

    A10=”Number of SIMPLE Jobs”
    B10=
    SUMPRODUCT((Data!$A$6:$A$2500>=B$6)*(Data!$A$6:$A$2500=B$6)*(Data!$A$6:$A$2500<C$6)*(Data!$E$6:$E$2500="Complex"))

    The Sumproduct formula in B10 calculate the number of "Simple" jobs falling between two dates.
    The Sumproduct formula in B11 calculate the number of "Complex" jobs falling between two dates.

    In this way we get the figures, about how much simple and complex jobs we have received so far in that particular week. Likewise we calculate weekly data for the whole month.

    But unfortunately the sumproduct formula is not looking for any duplicate job entries in Column B on the Data Sheet. For example some jobs are received only once, some twice some thrice and so on. The formula counts all, meeting the criteria.

    I have been asked to count the duplicate entries only once. For example in the above example:
    Column B= Job numbers (For example 4584160, 3926039, 3600528, 4524528, 4524528, 3926039, 3926039, 4512014 etc)
    If you closely look, you will notice that some job numbers are duplicated. I can easily figure out that there are 5 job numbers. However, the sumproduct formula will count them as 8.

    I hope I have made myself clear. Please help me figure out this formula. Once I get this, there are lot more to come, hopefully :)

    • Masood Ahmad says:

      A10=”Number of SIMPLE Jobs”
      B10=
      SUMPRODUCT((Data!$A$6:$A$2500>=B$6)*(Data!$A$6:$A$2500=B$6)*(Data!$A$6:$A$2500<C$6)*(Data!$E$6:$E$2500="Complex"))

      The Sumproduct formula in B10 calculate the number of "Simple" jobs falling between two dates.
      The Sumproduct formula in B11 calculate the number of "Complex" jobs falling between two dates.

Leave a Reply