Article updated on September 08, 2007

Question: How to count unique values in a list?

Column A (A1:A15) are random numbers between 0 and 10.

Answer:Cell B18 is {=SUMPRODUCT(1*(COUNTIF(A1:A15,A1:A15)=1))}
(Press CTRL+SHIFT+RETURN after the formula is typed in the cell)

COUNTIF(A1:A15,A1:A15) checks if a value is only represented once in the list (A1:A15). The result is either TRUE (1) or FALSE (0)

The sumproduct sums the true results.

Returns the sum of the products of the corresponding ranges or arrays

Counts the number of cells within a range that meet the given condition