counting number of unique items in a column

I'm looking for a simple way to find how many unique items are in a column. In my case it is the number of dates used. In the past, I have had only one or two dates, so I can count the number that match cell 2 (i.e. COUNTIF(B:B, $B$2)) If this is less than the total number of rows (minus header), I have assumed two days and proceed accordingly using two data blocks. Or I have manually blocked the dates as named matrices (IF(Bx=date1, VLOOKUP(Cx,day1,8), VLOOKUP(Cx,day2,8)). This is not a very good solution if a user uses more than two dates.

I am posting this in the general forum, and would prefer a spreadsheet solution. Right now, it seems easier to use VBA, but a lot of my users do not like the warnings Excel pops up what a spreadsheet has macros.

There is another thread going; started by MnMCarta on 11/14 which is exactly what I am looking for except without the criteria blocks. I am checking some of those options now, but I do not like array formulas because many of my users use the Enter key to browse and that can unknowingly erase array formulas.

You could use something like:

Formula:

Please Login or Register  to view this content.

modify ranges to suit but avoid use of entire column references with SUMPRODUCT.