Question:

I have a list of values (A1:A6), how do I count unique distinct values?

Answer:

Table of Contents

1. Count unique distinct values (case in-sensitive)
2. Count unique values (case in-sensitive)
3. Count unique distinct values (case sensitive)

Count unique distinct values (case in-sensitive)

Unique distinct values are all values but duplicates are merged into one distinct value. See picture below.

Unique distinct values are calculated in cell C1. Unique values are calculated in cell C3.

Array Formula in C1:

How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

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

EDIT: Alternative array formula in C1:

And another alternative array formula in C1:

Alternative array formula (No array formula!!)

If cell range also contains possible blank cells:

Alternative array formula (possible blank cells)

How the formula works

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

Step 1 - Count each value

COUNTIF(range, criteria) 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 {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

Step 2 - Invert values

1/COUNTIF(List1, List1)

becomes

1/ {3, 2, 3, 1, 2, 3}

and returns {1/3, 1/2, 1/3, 1, 1/2, 1/3}

Step 3 - Sum values

SUM(1/COUNTIF(List1, List1))

becomes

SUM({1/3, 1/2, 1/3, 1, 1/2, 1/3})

and returns 3.

1/3 + 1/2 + 1/3 + 1/1 + 1/2 + 1/3 = 3

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

Download excel example file

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

Recommended blog posts

If you are working with a large data set, array formulas demonstarated in this post may be too slow. This post describes how to count unique distinct values in a a large data set:
Count unique distinct values in a large dataset with a date criterion

Count unique values in a column

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

Array formula in C3:

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

Explaining formula in cell C3

=SUM(IF(COUNTIF(List1,List1)=1,1,0))

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)

IF(3, 2, 3, 1, 2, 3) = 1,1,0)
becomes
(0,0,0,1,0,0)

SUM(0,0,0,1,0,0) equals 1

0 + 0 + 0 + 1 + 0 + 0 = 1

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)

Recommended blog posts:

Count unique distinct records in excel 2007

Count unique distinct values (case sensitive)

Array formula in cell C2:

The array formula works with possible blanks in cell range A1:A9. The blank is not counted.

How to enter an array formula

1. Select cell C2
2. Click in formula bar
3. Paste above array formula
4. Press and hold Ctrl + Shift
5. Press Enter

Download excel *.xlsx file

Count unique distinct values case sensitive.xlsx

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:

Count unique distinct values using date criteria in a range in excel

Count unique distinct values in two columns in excel

Count duplicate distinct values in a column in excel

Count unique distinct values in three columns combined in excel

Count unique distinct values in two columns with date criteria in excel