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.
3 Responses to “How-to create an elegant, fun & useful Excel Tracker – Step by Step Tutorial”
Hi Chandoo,
I am responsible for tracking when church reports are submitted on time or not and the variations from the due date for submission.
Here is the Scenario;
The due date for the submission of monthly reports is on the 5th of each month. and I would like to know how many reports have been submitted on time (i.e, those that have been submitted on or before the due date) I would also want to track those reports that have been submitted after the due date has passed.
How can I create such a tracker?
Hi Chandoo,
I am a member of your excel school.
I was trying to create SOP Tracker I follow all your steps but I keep this error below.
The list source must be a delimited list, or a reference to a single row or cell.
I try looking on YouTube for answer but no luck.
can you help on this?
thanks
Carl.
Dear Mr. Chando,
Rakesh, I'm working in a private company in the UAE. Recently, I'm struggling to get more details about the staff sick, annual, unpaid, and leaves. I would like to get a tracker in excel. Could you please help me in this situation?
I also watching your videos in YouTube. i hope you can help me on this situation.