Search code examples
excelunique

Count unique values in a column in Excel

I have an .xls file with a column of data. How do I count how many unique values are contained in this column?

I have googled many options, but the formulas I've found 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 enter image description here

Solution

  • 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.