Counting Unique Occurrences in an Excel Spreadsheet

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

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
learn excel dashboards