Count unique values in a column in Excel

Question

I have an .xls file with a column with some data. How do I count how many unique values contains this column?

I have googled many options, but the formulas they give there always give me errors. For example,

=INDEX(List, MATCH(MIN(IF(COUNTIF($B$1:B1, List)=0, 1, MAX((COUNTIF(List, "<"&List)+1)*2))*(COUNTIF(List, "<"&List)+1)), COUNTIF(List, "<"&List)+1, 0))

returns 

Answer 1

Excel may be used to count unique data.
To count unique data in Excel, combine the SUM and COUNTIF functions. = SUM(1/COUNTIF(data, data)=1,1,0) is the syntax for this combination formula. The COUNTIF formula is used to count how many times each value in the range appears.

The resulting array looks like this {1;2;1;1;1;1}:  Divide the obtained values by 1 in the next step. The IF function implements the idea that this step will generate 1 if the values appear just once in the range, otherwise it will generate a fraction value. After that, the SUM function adds up all of the values and returns the result. Because this is an array formula, press Ctrl + Shift + Enter to assign it.