Saturday, February 8, 2014

Identify duplicate values in a list using "Countif"

Though MS Excel today allows to easily eliminate duplicate values in a list, have you ever tried to identify the number of entries that gets repeated in an excel list?

In the above example, we can identify the repeat entries using "COUNTIF" function.

Option 1

formula: =COUNTIF($D$1:D1,D1)
Explanation: This formula counts the occurrence of the value in the list i.e. on the first occurrence, the result will be one and on the second it will be 2 and like wise. (see the figure below)

Option 2

formula: =COUNTIF($D$1:$D$8,D1)
Explanation: This formula counts the total number of repetitions. If an entry appears thrice in a list, then the result will be 3 in all the occurrences of that entry. (see the figure below)

So start using whichever is convenient to you... :)



    The Summit is an educational Institution in Kolkata with a mission – to nurture the talent inside a student and prepare him/her for a bright career.

    The Summit has an excellent track record – more than 80% students have scored 90% and above in each of ICSE and ISC exams. We have on board a team of highly skilled faculty from reputed schools who tutor the students very carefully.

    We are the pioneers of the Mock Tests – a simulation of the ICSE / ISC / CBSE exams to hone the skills of the students before they appear for the ultimate.

    The Summit also offers regular coaching classes for class VI to XII, Crash Courses, Spoken English / Personality Development, JEE, Law Entrance Examination, IAS / IPS / WBCS and French course.

    Vist our website or call or email at

  2. very interesting post.this is my
    write my essay reviews first time visit here.i found so mmany interesting stuff in your blog especially its discussion..thanks for the post!

  3. This is the precise weblog for anybody who needs to seek out out about this topic. You notice so much its almost arduous to argue with you. You positively put a brand new spin on a subject that's been written about for years. Nice stuff, simply nice!

  4. Hey keep posting such good and meaningful articles.

  5. I don’t skills ought to I provide you with thanks! i'm altogether shocked by your article. You saved my time. Thanks 1,000,000 for sharing this text.

  6. Amazing web log and really fascinating stuff you bought here! I positively learned plenty from reading through a number of your earlier posts in addition and set to drop a discuss this one!

  7. Very attention-grabbing diary. lots of blogs I see recently do not extremely give something that attract others, however i am most positively fascinated by this one. simply thought that i'd post and allow you to apprehend.