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.
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
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/