How to Count the number of unique values in a list in excel

Published by Shoki Singh on

  1. How to Count the number of unique values in a list in excel

 

Let’s consider we have a long list of duplicate values in a range and our objective is to count only the unique occurrences of each value.

Though this can be a very common requirement in many scenarios but excel doesn’t have any single formula that can directly help us to count unique values in excel inside a range.

So, I will show you different ways of counting unique values in excel. Using Sumproduct and CountIF Formula. let’s start with an example, we have an excel sheet which contain employees name and their Department. In this sheet department names are repeating multiple time. and how to calculate count the number of unique department in this worksheet. we can see this worksheet in below.

The simplest and easiest way to count distinct values in excel is to use Sumproduct and CountIF formula. Following is the generic formula that you can use:

=SUMPRODUCT(1/COUNTIF(data,data))

‘data’ – data represents the range that contains the values

To get the result we need to use Formula : =SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))

Press Enter after writing the formula to get the desired output.

Let’s See How This Formula Works:

This formula consists of two functions combined together. Let’s first understand the role of inner CountIF function.

The CountIF function looks inside the data range (B2: B15) and counts the number of times each value appears in the data range. The result is an array of numbers and in our example, it might look something like this: {4;3;3;4;4;1;2;3;3;3;4;1;2;3} its means in range (B2:B15)

Quality department comes 4 times

HR department comes 3 times

IT department comes 1 time

Finance department comes 1 times

BD department comes 2 times and

Sales department comes 1 times

Next, the results of CountIF formula are used as a divisor with 1 as a numerator. This modifies the result of CountIF formula =1/COUNTIF(B2:B15,B2:B15)

Final step : after doing the above step, now we will use sumproduct formula with this

=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))

Excel do calculation like this. And you get your final result that is 6 unique count in the range (B2:B15)

1/4 + 1/3 + 1/3 + 1/4 + 1/4 + 1/1 + 1/2 + 1/3 + 1/3 + 1/3 + 1/4 + 1/1 + 1/2 + 1/3 = 72/12 = 6

If you like this trick please comment and share. thank you 🙂

you can download practice file from below link.

unique-department-count.xlsx 

84 Comments

Bridgette · February 7, 2019 at 2:12 pm

Remarkable! Its actually remarkable piece of writing, I
have got much clear idea concerning from this article.

do video games cause violence statistics · February 26, 2019 at 2:41 am

Heya i am for the first time here. I found this board and I find It
truly useful & it helped me out a lot. I hope to give something back and aid others like you aided me.

sachin · March 12, 2019 at 8:19 pm

Great. It clears my concern.

Senaida · March 22, 2019 at 12:14 pm

It is not my first time to pay a visit this web site,
i am browsing this web page dailly and obtain pleasant data from here
daily.

brawl stars guide reddit · March 28, 2019 at 2:06 am

Hi, I want to subscribe for this website to obtain newest updates, therefore where can i do it
please assist.

    Shoki Singh · March 28, 2019 at 7:51 am

    Thanku friend ☺️, I will definitely write new post soon.

icifinasteride.in.net · April 22, 2019 at 2:25 pm

First of all I want to say fantastic blog! I had a quick question in which I’d like to ask if you do not mind.
I was interested to find out how you center yourself and clear your mind prior to writing.
I’ve had a difficult time clearing my thoughts in getting my
thoughts out there. I do take pleasure in writing but it just seems like the first 10 to 15 minutes are usually lost simply
just trying to figure out how to begin. Any recommendations or hints?

Thanks!

justvenlafaxine.icu · April 22, 2019 at 2:40 pm

certainly like your web-site however you have to take a look at the
spelling on quite a few of your posts. Many of them are rife with spelling
problems and I find it very troublesome to tell the truth on the other hand I will surely come again again.

amoxicilina comprimidos on-line · April 22, 2019 at 3:11 pm

Hi, i think that i noticed you visited my weblog thus
i got here to return the want?.I am trying to to
find issues to enhance my site!I guess its good enough to make use of a few of your ideas!!

hierfluconazol.site · April 22, 2019 at 3:19 pm

Way cool! Some extremely valid points! I appreciate you penning
this post plus the rest of the website is very good.

MatEven · April 22, 2019 at 7:27 pm

Buy Tretinoin Cream Australia Cialis 20 Mg Foglietto Illustrativo Comprare Cialis In Italia

thearipiprazole.in.net · April 22, 2019 at 9:55 pm

For hottest news you have to visit web and on the
web I found this site as a best web page for latest updates.

EllJuiplY · May 7, 2019 at 11:34 am

Discount Plavix Coupons What Is Amoxicillin Used For viagra Xenical 120mg To Buy Online Generic Propecia 5mg Pharmacy

Skin Institute oosterhout · May 9, 2019 at 11:02 pm

Right away I am going to do my breakfast, later than having my breakfast coming yet again to read more news.

nagels · May 10, 2019 at 5:02 pm

First of all I want to say fantastic blog!
I had a quick question that I’d like to ask if you don’t mind.
I was curious to know how you center yourself and clear your mind before writing.
I have had difficulty clearing my mind in getting my ideas out there.
I do take pleasure in writing however it just seems like the first 10 to 15
minutes are generally lost just trying to figure out how to
begin. Any ideas or tips? Thank you!

Best Online Dating · May 12, 2019 at 1:18 pm

Hey just wanted to give you a quick heads up.

The text in your content seem to be running off the screen in Firefox.

I’m not sure if this is a formatting issue or something to do with web browser compatibility but
I thought I’d post to let you know. The design and style look great though!

Hope you get the problem fixed soon. Kudos

Nerdy Milf · May 12, 2019 at 2:48 pm

You’ve made some really good points there.

I checked on the web to learn more about the issue and found most individuals will go along with your views on this web site.

partner forsd · May 16, 2019 at 2:16 pm

Nice blog right here! Additionally your website rather a lot up very fast! What host are you using? Can I am getting your associate hyperlink in your host? I want my web site loaded up as fast as yours lol

izmir esford · May 16, 2019 at 6:08 pm

Thank you, I’ve just been searching for information approximately this topic for a while and yours is the greatest I’ve came upon so far. However, what about the conclusion? Are you certain concerning the supply?

alannarack · May 17, 2019 at 6:23 pm

Ahaa, its good dialogue regarding this piece of writing at this place at
this website, I have read all that, so now me also commenting at this place.

    Shoki Singh · May 19, 2019 at 3:14 pm

    Thanku so much for your great feedback @alannarack:)

seo · May 19, 2019 at 2:04 pm

Just want to say your article is as astonishing.
The clarity in your put up is just nice and i could suppose you’re
a professional on this subject. Fine with your permission allow me to take hold of your RSS
feed to stay updated with approaching post. Thanks a million and please carry
on the rewarding work.

    Shoki Singh · May 19, 2019 at 3:14 pm

    Thanku so much @Seo 😊🙏

Puffy Nipples · May 20, 2019 at 12:12 am

Someone essentially lend a hand to make significantly articles
I might state. That is the very first time I frequented your website page and up to now?

I surprised with the analysis you made to
make this particular publish extraordinary.

Fantastic activity!

Nude Females · May 21, 2019 at 9:59 am

I know this web page offers quality based content and other material, is there any other website which provides these kinds of stuff
in quality?

hoger in google · May 21, 2019 at 2:51 pm

Right now it looks like WordPress is the preferred blogging platform available right now.
(from what I’ve read) Is that what you’re using on your blog?

gamefly free trial · May 24, 2019 at 7:45 pm

When I initially commented I clicked the “Notify me when new comments are added” checkbox and
now each time a comment is added I get several e-mails with the same comment.
Is there any way you can remove me from that service?

Thank you!

how to get help in windows 10 · May 26, 2019 at 7:47 am

My brother suggested I might like this blog. He was totally right.
This post actually made my day. You can not imagine simply
how much time I had spent for this information! Thanks!

izmir eshot · June 1, 2019 at 12:08 am

I just want to mention I’m all new to blogs and certainly savored you’re web site. More than likely I’m want to bookmark your site . You surely have good writings. Appreciate it for sharing your web page.

izmir eshorts · June 1, 2019 at 3:00 am

How long does a copyright last on newspaper articles?. . If a service copies newspapers articles and then posts it in a database on the Internet, is there also a copyright on the Internet content?.

eshort izmir · June 1, 2019 at 4:08 am

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

esport · June 1, 2019 at 5:16 am

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

Bovenaan in Google · June 2, 2019 at 8:46 am

This is very interesting, You are a very skilled blogger.
I have joined your rss feed and look forward to seeking more
of your excellent post. Also, I have shared your website in my social networks!

Sexy Ass Cams · June 2, 2019 at 11:34 am

It’s awesome to pay a quick visit this site and reading the views of
all colleagues about this piece of writing, while I am also eager of getting familiarity.

http://canadianorderpharmacy.com/ · June 3, 2019 at 11:09 pm

I believe this is one of the so much important info for me. And i’m glad reading your article. But wanna observation on few basic things, The website taste is great, the articles is really great : D. Good task, cheers

esport · June 9, 2019 at 6:48 pm

How long does a copyright last on newspaper articles?. . If a service copies newspapers articles and then posts it in a database on the Internet, is there also a copyright on the Internet content?.

http://bit.do/eUxon · June 9, 2019 at 10:52 pm

Hi there it’s me, I am also visiting this web page on a regular basis, this site is really fastidious and the visitors are really sharing good thoughts.

orspu · June 10, 2019 at 10:34 pm

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

kalan · June 11, 2019 at 12:12 am

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

ford · June 11, 2019 at 1:19 am

Amazing blog layout here. Was it hard creating a nice looking website like this?

kievsi · June 11, 2019 at 2:15 pm

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

ciagra · June 13, 2019 at 3:37 pm

Amazing blog layout here. Was it hard creating a nice looking website like this?

hortes · June 15, 2019 at 5:36 pm

How long does a copyright last on newspaper articles?. . If a service copies newspapers articles and then posts it in a database on the Internet, is there also a copyright on the Internet content?.

pic · June 17, 2019 at 3:17 pm

Amazing blog layout here. Was it hard creating a nice looking website like this?

shop · June 18, 2019 at 2:33 pm

How long does a copyright last on newspaper articles?. . If a service copies newspapers articles and then posts it in a database on the Internet, is there also a copyright on the Internet content?.

pempe panter · June 19, 2019 at 9:23 am

I am not very superb with English but I find this very easygoing to translate.

online direk · June 20, 2019 at 2:24 pm

Amazing blog layout here. Was it hard creating a nice looking website like this?

man · June 21, 2019 at 1:04 am

How long does a copyright last on newspaper articles?. . If a service copies newspapers articles and then posts it in a database on the Internet, is there also a copyright on the Internet content?.

man · June 21, 2019 at 3:01 am

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

laiks · June 21, 2019 at 2:02 pm

Amazing blog layout here. Was it hard creating a nice looking website like this?

ysz · June 21, 2019 at 6:10 pm

thank you web site admin

kal · June 22, 2019 at 4:51 am

Amazing blog layout here. Was it hard creating a nice looking website like this?

zzp · June 22, 2019 at 8:40 pm

I love what you guys tend to be up too. This sort of clever work and coverage!
Keep up the very good works guys I’ve included you
guys to my blogroll.

pk · June 23, 2019 at 10:00 pm

Amazing blog layout here. Was it hard creating a nice looking website like this?

hot · June 24, 2019 at 6:20 am

Amazing blog layout here. Was it hard creating a nice looking website like this?

toret · June 24, 2019 at 7:48 am

I just want to mention I’m all new to blogs and certainly savored you’re web site. More than likely I’m want to bookmark your site . You surely have good writings. Appreciate it for sharing your web page.

re · June 24, 2019 at 3:12 pm

Your positions continually have got a lot of really up to date info. Where do you come up with this? Just saying you are very resourceful. Thanks again

childds · June 28, 2019 at 11:35 am

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

tori childs · June 30, 2019 at 6:46 am

Amazing blog layout here. Was it hard creating a nice looking website like this?

liseli child · June 30, 2019 at 8:55 am

I wish to show my gratitude for your kindness for individuals that require guidance on this one subject matter. Your very own dedication to getting the message all around appears to be really good and has in every case empowered ladies much like me to realize their objectives. Your personal invaluable help and advice indicates much to me and somewhat more to my colleagues. Thanks a lot; from all of us.

erkek child · June 30, 2019 at 10:59 am

I am usually to running a blog and i actually recognize your content. The article has really peaks my interest. I am going to bookmark your site and keep checking for new information.

torrent child · July 1, 2019 at 5:47 am

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

kondom · July 1, 2019 at 7:40 am

thank you web site admin

hapci · July 1, 2019 at 7:48 am

thank you web site admin

video · July 1, 2019 at 9:37 pm

thank you web site admin

hapci · July 1, 2019 at 10:15 pm

thank you web site admin

k child · July 2, 2019 at 3:53 am

Amazing blog layout here. Was it hard creating a nice looking website like this?

refresh kodunu götüne sokarım oda yetmedigi gibi tüm sitelerini sikerim · July 3, 2019 at 12:50 am

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

direksiyon · July 3, 2019 at 11:59 am

I wish to show my gratitude for your kindness for individuals that require guidance on this one subject matter. Your very own dedication to getting the message all around appears to be really good and has in every case empowered ladies much like me to realize their objectives. Your personal invaluable help and advice indicates much to me and somewhat more to my colleagues. Thanks a lot; from all of us.

yag yak · July 3, 2019 at 8:33 pm

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

eroksiyon · July 4, 2019 at 3:21 am

You made some decent factors there. I appeared on the internet for the issue and found most people will associate with with your website.

izmir escort · July 5, 2019 at 1:26 am

izmir escort sitemiz güzel bir sitedir.

korn childs · July 7, 2019 at 9:26 am

thank you web site admin

hulk childs · July 7, 2019 at 9:34 am

I wish to show my gratitude for your kindness for individuals that require guidance on this one subject matter. Your very own dedication to getting the message all around appears to be really good and has in every case empowered ladies much like me to realize their objectives. Your personal invaluable help and advice indicates much to me and somewhat more to my colleagues. Thanks a lot; from all of us.

kor childs · July 7, 2019 at 11:22 am

Amazing blog layout here. Was it hard creating a nice looking website like this?

going childs · July 10, 2019 at 8:44 am

I just want to say I am very new to blogs and truly savored you’re web site. More than likely I’m likely to bookmark your website . You amazingly come with superb articles and reviews. Regards for sharing your webpage.

pro childs · July 10, 2019 at 1:40 pm

thank you web site admin

seks childs · July 10, 2019 at 2:05 pm

I am not very superb with English but I find this very easygoing to translate.

포커 베팅 방법 · July 10, 2019 at 3:31 pm

I have read so many articles or reviews about the blogger lovers
except this article is in fact a pleasant article, keep it up.

on childs · July 11, 2019 at 7:41 am

I am not very superb with English but I find this very easygoing to translate.

top childs · July 13, 2019 at 9:36 pm

thank you web site admin

top childs · July 14, 2019 at 12:19 am

thank you web site admin

viagre childs · July 14, 2019 at 8:43 am

Amazing blog layout here. Was it hard creating a nice looking website like this?

intakram akgun porno · September 3, 2019 at 9:43 pm

thank you web site admin

Leave a Reply

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