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

Donation Link

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 1000 records or numbers it does not really matter which solution you take. But if you have more than 10000 records you should take Charles Williams' COUNTU function.

By the way: COUNTU needed 54 milliseconds to calculate the result 1000 (=1000 different numbers of 10000 random numbers). My UDF Pfreq needed 68 milliseconds to list all 1000 different entries with their number of occurrences (a total of 10000, of course)!

I think it is pretty evident that for this number of entries, simulations etc. you really should apply a VBA solution.