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;
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.
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.