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
Assuming 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.
- Getting unique items using data filter and formulas
- Use advanced data filters to find unique items
- Eliminate Duplicate Entries in a List using Formulas
- Get Unique items using Excel 2007 built in features
- Use pivot tables to get unique values from a range of cells
- Excel Remove Duplicates – Video Tutorial
- Find out if a list has duplicate values using Array formulas
Additional Resources:
- COUNTIF Excel Formula tutorial
- Learn cool uses of countif and sumif
- More exciting countif examples – Includes stuff like sorting text using formulas…
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.
69 Responses to “Count the number of unique values in a range [Quick Tip]”
very clever! thx
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!
basically it looks like the "unique" customers are only bart & homer!
@Chris.. You are welcome 🙂
@Spudinsane: I think it is in mind.. 😛 I can count Homer, Marge, Bart, Lisa, Skinner and Smithers.
@Spudinsane: no.. they are called as repeated customers. Try this: http://www.google.com/search?q=define%3A+unique
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. ; )
@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.
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.
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?
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.
@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.
@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
@JP agree.. Access is a better choice if you want to get uniques (or their counts) from a truckload of data. You can export data from excel to access very easily. Also check out how you can generate sql queries from excel data using formulas: http://chandoo.org/wp/2008/09/22/sql-insert-update-statements-from-csv-files/
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 ?
@Suman... Welcome to PHD. try this http://chandoo.org/wp/2009/03/25/using-array-formulas-example1/
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.
Wonderful...have been trying to work this one out for ages. Thanks very much!
Thanks for the great post. However, my requirement is to count the unique values excluding the hidden rows. Is there any solution?
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
@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.
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
@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
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
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
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?
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")
@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.
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?
Beautiful formula. Very guilty to have to search for it instead of thinking through it myself!
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)
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
@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")
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?
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]) & """"))"
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 🙂
[...] answered with a neat Sumproduct() based formula and quoting a post that Chandoo had written at Chandoo.org answering the question in [...]
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.
I agree with Bill. I need to count non-duplicate names in one column, based on conditions in another column.
Please Help
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?
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))
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
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?
@Dada
If your data starts in Row 3, use: =COUNTIF($A$2:$A$5,A2)
Copy down
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!
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)
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
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
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
Hi Aaron, you can use below formula to do this. Assuming your list is named list,
=sumproduct(1/countif(list,list)) - sumproduct(--(countif(list,list)=1))
Cool formula!
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
How can we develope a formula by using VBA code instead of this formula =SUMPRODUCT((A1:A30000”")/COUNTIF(A1:A30000,A1:A30000&”"))
@Sokchea
Have a read of:
http://stackoverflow.com/questions/1676068/count-unique-values-in-excel
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
@Noob... welcome to chandoo.org and thanks for your question.
You can use conditional formatting to highlight all duplicates. See attached file.
If your goal is to just remove them, I suggest using remove duplicates button in Data ribbon. Like this:
File with highlight example:
http://img.chandoo.org/playground/noob-highlight-repeat-projects.xlsx
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.
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.
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 🙂
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.
guys help me
i have this 5 rows
02 4 5 18 21 48
04 12 21 26 30 31
06 27 31 40 44 45
20 24 26 28 38 42
04 21 30 32 35 43
ok. Same like this number having Ranging from 1-49.
i want to calculate 6 numbers exactly from this 5 rows in range of 1-49. in excel
Guys help me out or provide such formula to calculate exact 6 number from this 5 rows with range of 1-49.
help me
Hi guys! I need an answer with this. Example I have this data in 1st worksheet:
1
1
2
2
3
On my 2nd sheet, i need to fill this.
No. of cells without duplicates: 1
No. of cells with duplicates: 2
What formula should I use?
@Arth
No. of cells without duplicates:
=SUM(IF(FREQUENCY(Sheet1!A1:A5,A1:A5)=1,FREQUENCY(Sheet1!A1:A5,A1:A5),)) Ctrl+Shift+Enter
or
=SUMPRODUCT(--(FREQUENCY(Sheet1!A1:A5,Sheet1!A1:A5)=1)) Enter
No. of cells with duplicates:
=SUM(IF(N(FREQUENCY(Sheet1!A1:A6,Sheet1!A1:A6)>1),N(FREQUENCY(Sheet1!A1:A6,Sheet1!A1:A6)>1),)) Ctrl+Shift+Enter
or
=SUMPRODUCT(N(FREQUENCY(Sheet1!A1:A6,Sheet1!A1:A6)>1)) Enter
Thank you so much 🙂
Hi!I'm so sorry to ask again. But what if there is another criteria like:
i have two cells for criteria = (1 is first cell and 2014 is second cell)
1 - 2014
1 - 2015
3 - 2014
1 - 2014
2 - 2014
Second sheet
for
No. of cells without duplicates 2014: 1
No. of cells with duplicates 2014: 2
Hi guys! I need an answer with this. Example I have this data in 1st worksheet:
Note: there are 2 cells, "-" symbolizes separation of the cell (i.e. 2014 1st cell, 1 2nd cell)
2014 - 1
2014 - 1
2015 - 2
2014 - 2
2014 - 3
On my 2nd sheet, i need to fill this.
No. of cells without duplicates 2014: 1
No. of cells with duplicates 2014: 2
What formula should I use?
Hi, Can someone help me with the below query:
I have two columns Name, pass/fail and Reference. I want a count of unique data basis of Name and Pass/fail column for the reference column and exclude blanks.
Eg: if name is "A" and the result is "Pass", i want a formula that will give me the unique count of reference. In this case, it will be 2 (which i counted manually). I have a huge data. so if there is an efficient way of doing it, please help me urgently.
Names Pass/Fail Reference
A Pass RA1
B Pass BA1
C Pass CA1
D Fail D2
A Fail RA1
B Pass BA1
C Fail CA2
D Pass D1
D Fail D1
D Pass D1
B
C Fail CA3
C
A Pass RA2
Dear,
Mr. Chandoo
I found this tip is very useful. But what if I want to apply filter on the list of the data, can the formula calculate according to the filtered data (e.g. only 4 unique values displayed)?
Thanks in advance for your brilliant answers.
Hello,
columnA
0
0
2
3
0
0
0
8
4
0
0
in example above, we have 2 adjacent non blank cells.
How to count adjacent non blank cells?
Thank you
mohsen
@Mohen
Try: =COUNTIF(A1:A11,">0")