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.
7 Responses to “CP014: How to create awesome dashboards – 10 step process for you”
Very interested in topic unfortunately I get no sound when I play it.
Where can I get add ins for excel 2007
#Budala
Try using Google to search for Excel Addin
I would like to join your email newsletter.
@Andy
Goto: http://chandoo.org/wp/
The newletter registration is top Right corner
Hello Chandoo,
i am one of the great fan of your. i am one MIS executive in realstate company.And i try make dash board for CRM(Client Relation Management) Dept formance and anlysis process.Its get created not so effective according to can u suggest me .what i hve to give or take for makeing it for effective and attractive?.
regards,
Shashak verma
Hi,
I want to create an interactive dashboard in excel (Google Drive) which should have multiple data along with beautiful charts..
I handle two teams in IT Tech support so were preparing 2 different dashboards.. however need to club and want to create single report, can anyone help something like