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... :)


  1. 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!

  2. Hey keep posting such good and meaningful articles.


Post a Comment

Popular posts from this blog

Keyboard Shortcut to add/delete rows or columns in Excel

Excel Keyboard Shortcuts - Handy during day to day work

Alternative for SUMIF and SUMIFS - Part 2 (SUMPRODUCT)