I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. 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-2011

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.