exceltoxl.com

Hi there,

I have a simple formula which gives a sum of distinct names in a list. Which means, unique names in a list. To get into that formula let's take an example when this function is required or useful.

Recently, I have started an e-commerce website by name www.deepshika.in here I have few sales representatives who sell my products to the retail shops.

Now, the sales reps are countable.

Let's say the number of retailers has been increased to 50 or 100 times from now. Is it easy for me to maintain with the current sales representatives. 

No! right?

...

Download Working File.xlsx

So, the sales reps will also increase along with their activities on day-to-day.

One fine day, if I want to track the number of sales reps working with me to manage my business or for cost cutting, I should know the employee count or sales rep count. (I will definitely get to know the count of my employees every month when I want to pay their salaries :) )

This is when the distinct count comes in picture to count the number of employees in my sales report.

I know this is worst example, but this is what I remember now. ;)

The formula used to find the distinct count of my employees is:

=SUMPRODUCT(1/COUNTIF((C3:C104),(C3:C104)))

WHAT THIS FORMULA DOES?

This formula will count the names in a range c3:c104 and display 1 if the name appears once, display 0.5 if the name appears twice, display's 0.33333 if the name appears thrice and so on...

Finally, it sums the numbers which gives the unique count of the sales reps or employee count.

That's it. Magic Works!!!

Note: The employee names are taken from here.

DID YOU FIND DISTINCT COUNT USEFUL?

If you find this useful, please do share it on your social networking sites by clicking on the icons placed around the blog. If you want to be a part of our family, take your FREE REGISTRATION from here.

SOME IMPORTANT LINKS:

Animation using in-cell chart

Change Gridline Colour

Thanks,