View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default Count the number of unique records

You can probably obtain a better performance score with:

=COUNTDIFF(IF($D$3:$D$10000-DAY($D$3:$D$10000)+1=$F5,$C$3:$C$10000,""),FALSE," ")

which must be confirmed with control+shift+enter, not just with enter.

Note 1. You need to download and install the morefunc.xll add-in.

Note 2. F5 must house the first day date of a month/year of interest.
For example: 1-Oct-2004. Take notice of 1 as day number.

Note 3. If C:D is sorted in ascending order on column D, we can do even
better qua performance.

wrote:
Wait, I lied.
=SUMPRODUCT(--(MONTH($D$3:$D$10000)=$F5),($C$3:$C$10000<"")/COUNTIF($C$3:$C$10000,$C$3:$C$10000&""))
(this is entered as an array formula)
Also, where $F5 is the month number in question (IE 12 for December)

This works, its just VERY cpu intensive.