Author: Oscar Cronquist Article last updated on January 12, 2023

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are merged into one distinct value.

To count unique distinct records, read this article: Count unique distinct records

You will also find a formula to count unique values, see the table of Contents below. Unique values are values that exist only once in a list or cell range. If a value has a duplicate they are not unique and not counted.

If you are working with lots of data I highly recommend using a pivot table. Excel 2013 and later versions allow you to count unique distinct values.

 

1.1 Count unique distinct values - Excel 365

count unique distinct values

This formula counts unique distinct values in cell range B3:B8, unique distinct values are all values except duplicate values.

For example, the value "France" in cell range B3:B8 has three instances in cells B3, B5, and B7, however, the value is only counted once.

The formula is not considering upper and lower letters, for example, "France" and "france" is the same value.

Excel 365 dynamic formula in cell D3:

=COUNTA(UNIQUE(B3:B8))

Explaining the formula in cell D3

Step 1 - List unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(B3:B8)

becomes

UNIQUE({"France";"Germany";"France";"Italy";"France";"Germany"})

and returns

{"France";"Germany";"Italy"}

Step 2 - Count nonempty values in array

The COUNTA function counts the non-empty or non-blank cells in a cell range.

Function syntax: COUNTA(value1, [value2], ...)

COUNTA(UNIQUE(B3:B8))

becomes

COUNTA({"France";"Germany";"Italy"})

and returns 3.

Back to top

1.2 Count unique distinct values in a cell range with blanks - Excel 365

count unique distinct values ignore blanks Excel 365

This example demonstrates how to count unique distinct values ignoring blank cells. The UNIQUE function returns 0 (zero) for blank values but the TOCOL function ignores blank values.

Excel 365 formula in cell D3:

=COUNTA(TOCOL(UNIQUE(B3:B8),1))

Explaining the formula in cell D3

Step 1 - List unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(B3:B8)

becomes

UNIQUE({"France";"Germany";0;"Italy";"France";"Germany"})

and returns

{"France";"Germany";0;"Italy"}

Step 2 - Ignore blank cells

The TOCOL function rearranges values in 2D cell ranges to a single column.

Function syntax: TOCOL(array, [ignore], [scan_by_col])

TOCOL(UNIQUE(B3:B8),1)

becomes

TOCOL({"France";"Germany";0;"Italy"},1)

and returns

{"France";"Germany";"Italy"}.

Step 3 - Count nonempty values in the array

The COUNTA function counts the non-empty or non-blank cells in a cell range.

Function syntax: COUNTA(value1, [value2], ...)

COUNTA(UNIQUE(B3:B8))

becomes

COUNTA({"France";"Germany";"Italy"})

and returns 3.

Back to top

1.3 Count unique distinct values in multiple nonadjacent cell ranges - Excel 365

count unique distinct values in tow columns

This example demonstrates an Excel 365 that counts unique distinct values in multiple cell ranges, the TOCOL function lets you add multiple cell ranges.

The cell ranges don't need to be the same size or adjacent or for that matter on the same worksheet, you can also reference a cell range containing multiple columns.

Excel 365 dynamic array formula in cell D14:

=COUNTA(UNIQUE(TOCOL((B3:B8,D3:D7))))

Explaining formula

Step 1 - Join cell ranges

The TOCOL function rearranges values in 2D cell ranges to a single column.

Function syntax: TOCOL(array, [ignore], [scan_by_col])

TOCOL((B3:B8,D3:D7))

becomes

({"AA"; "BB"; "AA"; "CC"; "BB"; "AA"}, {"BB"; "CC"; "DD"; "CC"; "BB"})

and returns

{"AA"; "BB"; "AA"; "CC"; "BB"; "AA"; "BB"; "CC"; "DD"; "CC"; "BB"}.

Step 2 - List unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(TOCOL((B3:B8,D3:D7)))

becomes

UNIQUE({"AA"; "BB"; "AA"; "CC"; "BB"; "AA"; "BB"; "CC"; "DD"; "CC"; "BB"})

and returns

{"AA";"BB";"CC";"DD"}.

Step 3 - Count nonempty values

The COUNTA function counts the non-empty or non-blank cells in a cell range.

Function syntax: COUNTA(value1, [value2], ...)

COUNTA(UNIQUE(TOCOL((B3:B8,D3:D7))))

Back to top

2.1 Count unique distinct values

The total number of unique distinct values are calculated in cell D3. The formula is not case sensitive, in other words, value FRANCE is the same as france.

Formula in cell E3:

=SUMPRODUCT(1/COUNTIF(B3:B8, B3:B8))

Recommended article:

Recommended articles

Count unique distinct values based on a condition
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]

Watch a video where I explain the formula

The following formula is an array formula although slightly smaller than the regular formula above, however, you need to enter it as an array formula.

=SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))

How to enter an array formula

Keep in mind that if you have a blank in your cell range the formulas above won't work, read this: Count unique distinct values in a cell range with blanks

Back to top

2.1.1 How the formula works

=SUMPRODUCT(1/COUNTIF($B$3:$B$8, $B$3:$B$8))

Step 1 - Count each value

COUNTIF($B$3:$B$8, $B$3:$B$8)

becomes

COUNTIF({"France"; "Germany"; "France"; "Italy"; "France"; "Germany"}, {"France"; "Germany"; "France"; "Italy"; "France"; "Germany"})

and returns {3, 2, 3, 1, 2, 3}. This array is shown in column C on the picture below.

Recommended articles

How to use the COUNTIF function
Counts the number of cells that meet a specific condition.

Step 2 - Divide 1 with array

1/COUNTIF($B$3:$B$8, $B$3:$B$8)

becomes

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

and returns {1/3, 1/2, 1/3, 1, 1/3, 1/2} This array is shown in column C on the picture below.

Step 3 - Sum values

SUMPRODUCT(1/COUNTIF($B$3:$B$8, $B$3:$B$8))

becomes

SUMPRODUCT({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

Recommended articles

How to use the SUM function
The SUM function in Excel allows you to add values, the function returns the sum in the cell it is […]

Back to top

2.2 Count unique distinct values in a cell range with blanks

Array formula in cell D3:

=SUM(IF($B$3:$B$8<>"",1/COUNTIF($B$3:$B$8, $B$3:$B$8), 0))

Watch a youtube video where I explain the formula

Alternative array formula

=COUNT(1/(--MATCH($B$3:$B$8,$B$3:$B$8,0)=MATCH(ROW($B$3:$B$8),ROW($B$3:$B$8))))

Recommended article

Recommended articles

Count unique distinct records
The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]

Back to top

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.

Recommended articles

A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.

Back to top

Get excel example file

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

Back to top

2.3 Count unique distinct values (case sensitive)

Array formula in cell D4:

=SUM(1/MMULT(EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))*1, ROW($B$3:$B$11)^0))

Watch a video where I explain the formula

Recommended article

Extract unique distinct values (case sensitive) [Formula]

Recommended articles

5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Back to top

How to enter an array formula

  1. Select cell C2
  2. Press with left mouse button on in formula bar
  3. Paste above array formula
  4. Press and hold Ctrl + Shift
  5. Press Enter

Back to top

2.3.1 Explaining array formula in cell D4

Step 1 - Compare values against each other using the EXACT function

EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))

becomes

=EXACT({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"}, {"Aa", "CC", "AA", "BB", "BB", "EE", "bb", "Aa", "aa"})

and returns the following boolean array, shown in picture below. Boolean values are TRUE or FALSE.

I have added the original values horizontally and vertically, they are also bolded.

The first column shows that value Aa exists twice because there are two TRUE in the first row.

Step 2 - Add values row-wise

The MMULT function allows you to add numbers for each row. To be able to do that I need to convert the boolean values to integers, in this case 0 or 1.

The MMULT function needs two arguments, the second argument must be an array of 1's with the same number of rows as the array in the first argument.

MMULT(EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))*1, ROW($B$3:$B$11)^0)

becomes

=MMULT({1, 0, 0, 0, 0, 0, 0, 1, 0; 0, 1, 0, 0, 0, 0, 0, 0, 0; 0, 0, 1, 0, 0, 0, 0, 0, 0; 0, 0, 0, 1, 1, 0, 0, 0, 0; 0, 0, 0, 1, 1, 0, 0, 0, 0; 0, 0, 0, 0, 0, 1, 0, 0, 0; 0, 0, 0, 0, 0, 0, 1, 0, 0; 1, 0, 0, 0, 0, 0, 0, 1, 0; 0, 0, 0, 0, 0, 0, 0, 0, 1}, {1; 1; 1; 1; 1; 1; 1; 1; 1})

and returns {2; 1; 1; 2; 2; 1; 1; 2; 1}. This array is shown in the column to the right.

Step 3 - Divide 1 with array

1/MMULT(EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))*1, ROW($B$3:$B$11)^0)

returns {0.5; 1; 1; 0.5; 0.5; 1; 1; 0.5; 1}

Step 4 - Sum values

=SUM(--(MMULT(EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))*1, ROW($B$3:$B$11)^0)=1))

becomes

=SUM({0.5; 1; 1; 0.5; 0.5; 1; 1; 0.5; 1})

and returns 7 in cell D4.

Back to top

Get excel *.xlsx file

Count unique distinct values case sensitive.xlsx

Back to top

2.4 Count unique distinct values in two columns

Formula in C12:

=SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))

How to create an array formula

  1. Double press with left mouse button on cell C12
  2. Paste above formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

Explaining formula in cell C12

Step 1 - Count values in cell range B3:B8

The COUNTIF function counts values equal to a condition or criteria.

COUNTIF($B$3:$B$8, $B$3:$B$8)

becomes

COUNTIF({"AA";"BB";"AA";"CC";"BB";"AA"}, {"AA";"BB";"AA";"CC";"BB";"AA"})

and returns

{3;2;3;1;2;3}

Step 2 - Divide 1 with array

1/COUNTIF($B$3:$B$8, $B$3:$B$8)

becomes

1/{3;2;3;1;2;3}

and returns

{0.333333333333333;0.5;0.333333333333333;1;0.5;0.333333333333333}

Step 3 - Sum values

The SUM function simply adds the numbers and returns the total.

SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))

becomes

SUM({0.333333333333333;0.5;0.333333333333333;1;0.5;0.333333333333333})

and returns 3.

Step 4 - Which values exist in cell range $D$3:$D$8

COUNTIF($B$3:$B$8, $D$3:$D$8)=0

becomes

COUNTIF({"AA";"BB";"AA";"CC";"BB";"AA"}, {"BB";"CC";"DD";"CC";"BB";"BB"})=0

becomes

{2;1;0;1;2;2}=0

and returns

{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}

Step 5 - Convert TRUE to corresponding number

The IF function uses a logical expression (argument1) to determine which value to return (TRUE - argument2 , FALSE - argument3)

IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)

becomes

IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)

becomes

IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {0.333333333333333;0.5;1;0.5;0.333333333333333;0.333333333333333}, 0)

and returns

{0;0;1;0;0;0}.

Step 6 - Sum array

SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))

becomes

SUM({0;0;1;0;0;0})

and returns 1.

Step 7 - Add numbers

SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))

becomes

3+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0))

becomes

3+1

and returns 4 in cell D12.

Get Excel *.xlsx file

Count unique distinct values in two columns.xlsx

Back to top

3.1 Count unique values in a column

Unique values are values that exist only once, see picture below.

AA has a duplicate and is not unique. BB and CC have no duplicates and are unique.

Formula in D3:

=SUMPRODUCT(--(COUNTIF($B$3:$B$8,$B$3:$B$8)=1))

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

Watch a video where I explain the formula

Back to top

Explaining formula in cell C3

Step 1 - Count all values

The COUNTIF function counts how many values in a cell range that match a condition. In this case, the second argument has multiple values and this makes the COUNTIF function return an array of values.

COUNTIF($B$3:$B$8,$B$3:$B$8)

becomes

COUNTIF({"France"; "Germany"; "France"; "Italy"; "France"; "Germany"},{"France"; "Germany"; "France"; "Italy"; "France"; "Germany"})

and returns the array: {3; 2; 3; 1; 3; 2}

This array tells us that the value France exists three times in cell range B3:B8. Germany exists twice and Italy is a unique value meaning there is only one instance of Italy in cell range B3:B8.

Learn more about the COUNTIF function here:

Recommended articles

How to use the COUNTIF function
Counts the number of cells that meet a specific condition.

Step 2 - Check each value is equal to 1

COUNTIF($B$3:$B$8,$B$3:$B$8)=1

becomes

{3; 2; 3; 1; 3; 2}=1

and returns {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}

Step 3 - Convert boolean values to integers

To be able to sum the boolean values I need to convert them to their equivalent 0 and 1. TRUE is 1 and FALSE is 0.

--(COUNTIF($B$3:$B$8,$B$3:$B$8)=1)

becomes

--({FALSE; FALSE; FALSE; TRUE; FALSE; FALSE})

and returns {0; 0; 0; 0; 0; 0; 1; 0; 0}

Step 4 - Sum values

=SUMPRODUCT(--(COUNTIF($B$3:$B$8,$B$3:$B$8)=1))

becomes

=SUMPRODUCT({0; 0; 0; 0; 0; 0; 1; 0; 0})

and returns 1 in cell D3.

Back to top

Get excel example file

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

Back to top

Recommended blog posts:

Count unique distinct records in Excel

3.2 Count unique values (case sensitive)

The picture below shows values in column B, the formula in cell D3 counts 5 unique values in column B.

They are CC, AA, EE, bb and aa. They only exist once in column B.

Aa and BB have duplicates and are not unique.

Array formula in cell D3:

=SUM(--(MMULT(EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))*1, ROW($B$3:$B$11)^0)=1))

How to enter an array formula

Watch a video where I explain the formula

Back to top

2.2.1 Explaining array formula in cell D3

Step 1 - Compare values against each other using the EXACT function

EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))

becomes

=EXACT({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"}, {"Aa", "CC", "AA", "BB", "BB", "EE", "bb", "Aa", "aa"})

and returns the following boolean array, shown in picture below. Boolean values are TRUE or FALSE.

I have added the original values horizontally and vertically, they are also bolded.

The first column shows that value Aa exists twice because there are two TRUE in the first row.

Step 2 - Add values row-wise

The MMULT function allows you to add numbers for each row. To be able to do that I need to convert the boolean values to integers, in this case 0 or 1.

The MMULT function needs two arguments, the second argument must be an array of 1's with the same number of rows as the array in the first argument.

MMULT(EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))*1, ROW($B$3:$B$11)^0)

becomes

=MMULT({1, 0, 0, 0, 0, 0, 0, 1, 0; 0, 1, 0, 0, 0, 0, 0, 0, 0; 0, 0, 1, 0, 0, 0, 0, 0, 0; 0, 0, 0, 1, 1, 0, 0, 0, 0; 0, 0, 0, 1, 1, 0, 0, 0, 0; 0, 0, 0, 0, 0, 1, 0, 0, 0; 0, 0, 0, 0, 0, 0, 1, 0, 0; 1, 0, 0, 0, 0, 0, 0, 1, 0; 0, 0, 0, 0, 0, 0, 0, 0, 1}, {1; 1; 1; 1; 1; 1; 1; 1; 1})

and returns {2; 1; 1; 2; 2; 1; 1; 2; 1}. This array is shown in the column to the right.

Step 3 - Check if values in array are equal to 1

MMULT(EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))*1, ROW($B$3:$B$11)^0)=1

returns {FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE}

Step 4 - Convert boolean values to integers

The SUM function can't add boolean values, to be able to do that I need to convert boolean values to integers.

--(MMULT(EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))*1, ROW($B$3:$B$11)^0)=1)

returns {0;1;1;0;0;1;1;0;1}

Step 5 - Sum values

=SUM(--(MMULT(EXACT($B$3:$B$11, TRANSPOSE($B$3:$B$11))*1, ROW($B$3:$B$11)^0)=1))

becomes

=SUM({0;1;1;0;0;1;1;0;1})

and returns 5 in cell D3.

Back to top

Get excel *.xlsx file

Count unique values case sensitive.xlsx

Back to top