Example: Suppose A1:A10000 contain 10,000 random characters from A thru H. With two helper columns you can create a list of unique entries without array formulas.
Please note that the normal formula in E1 should have read
I just prefer to count or to sum conditionally with SUMPRODUCT because the number of conditions might get increased - and then it might be impossible to use COUNTIF any longer. See here, please. Other examples on COUNTIF you can see at RANK and at Largest Remainder.
This worksheet function approach needs a runtime of O(n^2) because of the COUNTIFs in column B. My UDFs sbUniq and Pfreq only need a runtime of O(n).
If you are interested in downloading a 408 KB Excel 2010 sample file go to my Download page, please.