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

How the formula works

=SUM(1/COUNTIF(List1, List1))

COUNTIF(List1, List1) counts the number of cells within a range that meet the given condition.

COUNTIF(List1, List1) becomes COUNTIF((AA, BB, AA, CC, BB, AA), (AA, BB, AA, CC, BB, AA)) and returns the array:
(3, 2, 3, 1, 2, 3)

AA - 3 (AA exists 3 times in range List1)
BB - 2 (BB exists twice in range List1)
AA - 3
CC - 1
BB - 2
AA - 3

=SUM(1/(3, 2, 3, 1, 2, 3)) becomes SUM(1/3, 1/2, 1/3, 1/1, 1/2, 1/3)

SUM(1/3, 1/2, 1/3, 1/1, 1/2, 1/3) = 3

Count unique values in a column

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

• Count unique values in a column in excel
• Count unique distinct values in two columns in excel
• Count unique distinct values in three columns combined in excel
• Count unique values and unique distinct values in three ranges combined in excel
• Count unique and unique distinct values in a multicolumn range in excel
  
• Count unique values and unique distinct values in two ranges combined
• How to count unique combined column values
• How to count unique distinct records in a date range
  
• Count unique distinct records in a date range and a numeric range in excel
  
• Count unique distinct values in two columns with date criteria in excel
  
• Count unique distinct months in excel
• Count duplicate distinct values in a column in excel

Related posts:

1. Count unique distinct values using date criteria in a range in excel
2. Count unique values in two lists combined in excel
3. Count unique distinct values within same week, month or year in excel
4. Count unique distinct months in excel