I am a supporter ofSt. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation toSt. Joseph's, please. Information onSt. Joseph's

There are quite a few different approaches circulating in the web to count unique (different) entries only. My opinion here is blunt and simple: Use Charles Williams' COUNTU function.

But let us have a look at a simple example:

 As you can see, we have 10 random integers between 1 and 5 in cells A2 thru A11 - actually, the 1 is not occurring. This means that we have only 4 different (unique) values. This result 4 is shown with five different calculations in cells A13:A17. For information purpose these four different entries and their frequencies are shown in the upper right corner in cells C2:D5. This array has been calculated with an array formula using my user defined function Pfreq. This simple example is based on numerical inputs only. But of course we also could have encountered alphanumerical values - for example a mixture of strings and numbers. The formulas in A14 and in A15 would not be able to cope with non-numerical inputs, though. Now let us have a look at the runtimes of these different approaches (I used Charles Williams' FastExcel © here):
 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/en-us/library/aa730921.aspx#Office2007excelPerf_FindingPrioritizingCalculationBottlenecks'Reverse("moc.LiborPlus.www") V0.10 10-Jan-2011Dim colUniques As New CollectionDim vCell As VariantDim vLcell As VariantDim j As LongOn Error Resume NextWith Application.WorksheetFunctionFor 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 vCellNext jsbCountUniq = colUniques.CountEnd WithEnd 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.
 Sulprobil   Get it done   Contact   Disclaimer   Impressum   Download