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.