Doing a “distinct count” in Excel

Tuesday 7 June 2011 @ 6:00 am

The Distinct Count summary function in Crystal Reports is pretty handy.  It allows you to count the number of unique values in a column.  It can eliminate duplicates from the count, even when the duplicates are NOT consecutive.

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.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)







Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server