Count the Unique Entries in a range

3 Flares 3 Flares ×

A common question from Excel analysts and enthusiasts on my courses is to count the number of unique entries 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.

List of delegates

Using SUMPRODUCT to Count Unique Entries

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.

Count the unique entries in a range

The answer appears as 4. There are 4 different delegates (Mickey Mouse, Bill Ding, Belle Jinwaffles and Minnie Mouse).


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.

Error caused by blank row

The formula can be adapted to ignore any spaces in the range.

Count the unique entries ignoring blanks

Leave a Reply

Your email address will not be published. Required fields are marked *