For less than 1,000 records or numbers it does not really matter which solution you take. But if you have more than some thousand records you should take Charles Williams' COUNTU function.
By the way: COUNTU needed 54 milliseconds to calculate the result 1,000 (=1,000 different numbers of 10,000 random numbers). My UDF Pfreq needed 68 milliseconds to list all 1000 different entries with their number of occurrences (a total of 10,000, of course)!
I think it is pretty evident that for this number of entries, simulations etc. you really should apply a VBA solution.
A more flexible function than COUNTU is sbCountUniq which works on ranges over more than one workbook as well as on an array:
Function sbCountUniq(ParamArray v() As Variant) As Long 'Count unique values over all input areas (ranges or one array). 'Inspired by Charles Williams' COUNTU function, see: 'http://msdn.microsoft.com/enus/library/aa730921.aspx#Office2007excelPerf_FindingPrioritizingCalculationBottlenecks 'Reverse("moc.LiborPlus.www") V0.10 10Jan2011
Dim colUniques As New Collection Dim vCell As Variant Dim vLcell As Variant Dim j As Long
On Error Resume Next With Application.WorksheetFunction For j = LBound(v) To UBound(v) For Each vCell In v(j) If vCell <> vLcell Then If Len(CStr(vCell)) > 0 Then colUniques.Add vCell, CStr(vCell) End If End If vLcell = vCell Next vCell Next j sbCountUniq = colUniques.Count End With End Function
BTW: Please use my UDF sbCVU if you like to count visible unique cells only. If you need to count unique values with criteria, look here, please.
