SubscribeCount the number of unique values in a range [Quick Tip]
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.
| ||||
|
| ||||
|
Leave a Reply
 |
Excel Time Sheets and Resource Management [Project Management using Excel - Part 4 of 6] | What would you do if a co-worker makes ugly chart? [weekend poll] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
42 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?
@JP… you are pushing excel calculation limits now
. 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