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.

unique-distinct-values

Array Formula in C1:

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

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:

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

And another alternative array formula in C1:

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

Alternative array formula (No array formula!!)

=SUMPRODUCT(1/COUNTIF(List1, List1)) + ENTER

If cell range also contains possible blank cells:

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

Alternative array formula (possible blank cells)

=COUNT(1/(--MATCH(List1,List1,0)=MATCH(ROW(List1),ROW(List1)))) + CTRL + SHIFT + ENTER

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:

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

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:

=SUM(IFERROR(1/IF($A$1:$A$9<>"", FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($A$1:$A$9)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))), 0), 0))

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