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.