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

To count the number of different values in A2:A100 (not counting blanks):

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))

---

Copied from an answer by @Ulli Schmid to What is this COUNTIF() formula doing?:

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

Counts unique cells within A1:A100, excluding blank cells and ones with an empty string ("").

How does it do that? Example:

A1:A100 = [1, 1, 2, "apple", "peach", "apple", "", "", -, -, -, ...] then: A1:A100&"" = ["1", "1", "2", "apple", "peach", "apple", "", "", "", "", "", ...] 

so this &"" is needed to turn blank cells (-) into empty strings (""). If you were to count directly using blank cells, COUNTIF() returns 0. Using the trick, both "" and - are counted as the same:

COUNTIF(A1:A100,A1:A100) = [2, 2, 1, 2, 1, 2, 94, 94, 0, 0, 0, ...] but: COUNTIF(A1:A100,A1:A100&"") = [2, 2, 1, 2, 1, 2, 94, 94, 94, 94, 94, ...] 

If we now want to get the count of all unique cells, excluding blanks and "", we can divide

(A1:A100<>""), which is [1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, ...] 

by our intermediate result, COUNTIF(A1:A100,A1:A100&""), and sum up over the values.

SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))   = (1/2 + 1/2 + 1/1 + 1/2 + 1/1 + 1/2 + 0/94 + 0/94 + 0/94 + 0/94 + 0/94 + ...) = 4 

Had we used COUNTIF(A1:A100,A1:A100) instead of COUNTIF(A1:A100,A1:A100&""), then some of those 0/94 would have been 0/0. As division by zero is not allowed, we would have thrown an error.

Answer 2

try - =SUM(IF(FREQUENCY(MATCH(COLUMNRANGE,COLUMNRANGE,0),MATCH(COLUMNRANGE,COLUMNRANGE,0))>0,1))

where COLUMNRANGE = the range where you have these values.

e.g. - =SUM(IF(FREQUENCY(MATCH(C12:C26,C12:C26,0),MATCH(C12:C26,C12:C26,0))>0,1))

Press Ctrl+Shift+Enter to make the formula an array (won't calculate correctly otherwise)