Excel formula for a Distinct Count

Firstly, if all you want to do is filter out duplicates you can use the Advanced Filter tool to do so. Go to the Data tab, Sort & Filter, Advanced and select the ‘Unique records only’ tick box.

 

UniqueRecordsOnly

 

However, if you want to return the distinct count of a range then you need to use an array formula.

 

I used to use a long-winded method involving the FREQUENCY and MATCH functions until I discovered this more simple alternative.

 

Enter this formula (replacing “range” with the relevant cell references or named range).

 

=SUM(1/COUNTIF(range,range))

 

Then turn this into an array formula by selecting the cell, clicking into the formula bar, and then pressing Ctrl+Shift+Enter.

2 Comments on “Excel formula for a Distinct Count

  1. Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword….wait there’s even more Now what if i told you there was a simple WordPress plugin that does all the On-Page SEO, and automatically for you? That’s right AUTOMATICALLY, just watch this 4minute video for more information at. Seo Plugin

  2. 519957 506879Private Krankenversicherung – Nur dann, wenn Sie sich fr die Absicherung ber die Rentenversicherung entschieden haben, dann knnen Sie sich sicher sein, dass Sie im Alter so viel Geld haben, damit Sie Ihren Lebensstandard halten knnen. 954141

Leave a Reply

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