Count Unique Values

{=SUM(1/COUNTIF(A1:A10,A1:A10))}

Push Ctrl, Shift and Enter to make this an array formula.

This returns an #DIV/0! error so if there are blanks, you can use something like this.

{=SUM(IF(A1:A10="","",1/COUNTIF(A1:A10,A1:A10)))}

Push Ctrl, Shift and Enter to make this an array formula.

or

{=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))}

Push Ctrl, Shift and Enter to make this an array formula.

or

{=SUM(IF(COUNTIF(A1:A10,A1:A10)=1,1,0))}

Push Ctrl, Shift and Enter to make this an array formula.

or

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))