Counting Unique Occurrences in an Excel Spreadsheet

January 28, 2008 – 11:52 pm by JP

This formula, entered as an array (Ctrl-Shift-Enter) in a single cell, will show you if there any duplicate entries in a given range (in this case, A1:C100). It is wrapped in an IF function to provide a friendly message; a kind of in-cell error handling, if you will. I usually do this when I know others are going to use the formula, to make it easy for them to understand the output. Otherwise it just gives you the number of unique entries which some people may not know what to do with.

=IF(COUNTA(A1:C100)=SUM(1/COUNTIF(A1:C100,A1:C100)),”No duplicates”,”Some duplicates”)
The formula first counts the number of used cells in the range (COUNTA function). Then compares it to the array portion of the formula which counts unique entries (from http://support.microsoft.com/kb/823573

).

The range must not contain blanks. If it does, you get a #DIV/0! error. If you are working with a range that contains blanks, or if you are giving out the formula to others, use this instead:

=IF(COUNTA(A1:C100)=SUM(IF(LEN(A1:C100),1/COUNTIF(A1:C100,A1:C100))),”NO duplicates”,”Some duplicates”)

That way when someone creates a blank in the range, they won’t come running to you to see why the formula is broken.

You could also give a count of the number of duplicates, but I discourage this as it slows down the workbook considerably, since you are checking the array twice:

=IF(COUNTA(A1:C100)=SUM(IF(LEN(A1:C100),1/COUNTIF(A1:C100,A1:C100))),”No duplicates”,COUNTA(A1:C100)-SUM(IF(LEN(A1:C100),1/COUNTIF(A1:C100,A1:C100))) &” duplicates”)

–JP

If you enjoyed this page: