Tuesday, June 07, 2011

Finding Duplicates in Excel

One of my customers wanted to do the same type of summary in an Excel spreadsheet. We were both surprised that there was no function like this in Excel. But after a few Google searches I came up with a workaround that lets an Excel formula do the same thing as the Distinct Count summary function in Crystal:

=SUMPRODUCT((A1:A99<>"")/COUNTIF(A1:A99,A1:A99&""))

You replace the three sample ranges “A1: A99″ with whatever your data range is. I am not sure I could explain why it works, but it does.

No comments: