A common question from Excel analysts and enthusiasts on my courses is to count unique values in a list.
This post looks at using a formula to calculate this distinct count.
Consider the list below of a list of delegates attending our courses. A normal count on this range will tell us how many attendances there were. That’s good, but we want to know how many unique attendees there were.
Using SUMPRODUCT to Count Unique Values
If you have not been introduced to the amazing world of the SUMPRODUCT function before then you are in for a treat.
This function comes to our rescue on so many occasions.
The image below shows the formula to count the number of different delegates that attended.
The answer appears as 4. There are 4 different delegates (Mickey Mouse, Bill Ding, Belle Jinwaffles and Minnie Mouse).
Count Unique Formula Explanation
Ok, let’s try and explain what is going on here.
The COUNTIF function produces the result below;
{3;2;1;3;1;3;2}
This is because Bill Ding appears 3 times, Belle Jinwaffles twice, then Mickey Mouse once etc. So it is counting how many times each different delegate attended.
These figures are then divided by 1 so that when summed together we are adding 1 for each delegate. The array below is summed.
{0.33;0.5;1;0.33;1;0.33;0.5}
Not the easiest formula to get your head around. Most importantly it works. An understanding can come with time if it seems tricky right now.
Watch the Video
Tackling Spaces in the Range
Spaces in the range will cause the #DIV/0! error to appear.
The formula can be adapted to ignore any spaces in the range.
PivotTables provide another neat way to count unique values as they have a distinct count calculation. There is a trick to enable this functionality though.
This is fine if someone knows how to work with formulas.
However, I try to work out in a different way.
Let us say the data in consideration is in column A. I would copy that data into column B. Then remove duplicates column b. This will leave only unique names and then count the remaining records.
I understand this works if we are to work with limited columns only. In case where there are multiple columns to be checked, your formula works.
Thanks for sharing this.
Thank you for sharing Paresh. It is good to know different approaches for different scenarios.
The formula is also automated, so if the data changes regularly it will look after itself. Your approach would require the manual task each and every time. However, if the data doesn’t change regularly then your approach is better because the formula is unnecessary.