Count unique distinct values in a column in excel

Filed in Excel on Jun.09, 2009.

Question: I have a list of values of which I extract an unique distinct list. How do I count that unique distinct list of values?

Answer:

Unique distinct values are all values in a range or column but duplicates are merged into one distinct value. See picture below.

Formula in C1:

=SUM(IF(MATCH(List1, List1, 0)>=(ROW(List1)-MIN(ROW(List1))+1), 1, 0)) + CTRL + SHIFT + ENTER

There are three distinct unique values. (AA, BB and CC)

EDIT: Alternative formula in C1:

=SUM(IF(FREQUENCY(COUNTIF(List1, "<"&List1), COUNTIF(List1, "<"&List1))>0, 1, 0)) + CTRL + SHIFT + ENTER

And another alternative formula in C1:

=SUM(1/COUNTIF(List1, List1)) + CTRL + SHIFT + ENTER

Unique values are values existing only once in a list or range. See picture below.

Formula in C3:

=SUM(IF(COUNTIF(List1;List1)=1;1;0)) + CTRL + SHIFT + ENTER

There are only one unique value (CC) in the list, all other values have duplicates.

Named ranges
List1 (A1:A6)
What is named ranges?

How to customize the formula to your excel workbook
Change the named ranges.

Download excel example file
count-unique-distinct-values-in-a-column.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this blog post:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

SUM(number1,[number2],)
Adds all the numbers in a range of cells

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

This blog article is one out of twelve articles on the same subject "count unique and unique distinct".

Share and Enjoy:

Most Popular