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