## Count unique distinct values

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.

**Table of Contents**

**Count unique distinct values****Count unique values**

**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:**

Recommended article:

Count unique distinct values based on a condition

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

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

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

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

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

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

**Count unique distinct values in a cell range with blanks**

**Array formula in cell D3:**

Watch a youtube video where I explain the formula

**Alternative array formula**

Recommended article

This example sheet has 3 columns with some random data. It is quite complicated trying to manually count unique distinct […]

#### How to create an array formula

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

Array formulas allows you to do advanced calculations not possible with regular formulas.

**Download excel example file**

count-unique-distinct-values-in-a-column.xls

(Excel 97-2003 Workbook *.xls)

**Count unique distinct values (case sensitive)**

**Array formula in cell D4:**

#### Watch a video where I explain the formula

Recommended article

Extract unique distinct values (case sensitive) [Formula]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

**How to enter an array formula**

- Select cell C2
- Click in formula bar
- Paste above array formula
- Press and hold Ctrl + Shift
- Press Enter

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

**Download excel *.xlsx file**

Count unique distinct values case sensitive.xlsx

### 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:**

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

#### Watch a video where I explain the formula

**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:

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.

**Download excel example fil****e**

count-unique-distinct-values-in-a-column.xls

(Excel 97-2003 Workbook *.xls)

**Recommended blog posts:**

Count unique distinct records in Excel

### 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:**

#### Watch a video where I explain the formula

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

#### Download excel *.xlsx file

Count unique 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**

Filter unique distinct and duplicate values from a large data set

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Count unique distinct values in two columns

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

### 27 Responses to “Count unique distinct values”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

It is the same theme in my previous comment. The ability to count unique entries with blank cells in the range.

[...] Count unique distinct values in a column in excel [...]

Can it be case sensitive?

AA

BB

aa

CC

BB

Aa

Yes, you can =UPPER(text) and then you Oscar's method

Andy,

Array formula in cell E5:

Hi Oscar, i'm trying to use named range in excel 2007, but i'm getting error message "The formula you typed contains an error" (i'm using your formula). Do you know how to fix it? Is it supported in mic. excel 2007?

Nike,

Can you provide the formula?

I'm using your formula "=SUM(IF(MATCH(List1|List1|0)>=(ROW(List1)-MIN(ROW(List1))+1)|1|0))". I don't why why i'm getting your error if i'm using named range, but if i change it to "=SUM(IF(MATCH($B$2:$B$7|$B$2:$B$7|0)>=(ROW($B$2:$B$7)-MIN(ROW($B$2:$B$7))+1)|1|0))", it's working

*your error = an error

Nike,

Your formula:

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

Should be:

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

Oscar, i changed the "list separator" from "," to "|" in Regional and Languages option setting. But when i change it again to ",", the formula is working using named range.So named ranged won't working with "|".

Thank you Oscar for your help :)

Nike,

I am sorry, I didn´t understand that it was a "list separator". I am almost sure it works with "|". Something else must be wrong, in my opinion.

Andy,

Great question, I don´t have an answer yet.

Breathtakingly simple and elegant solution:

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

Many thanks

Thanks, this is great stuff. Much better than the stuff over at https://office.microsoft.com/en-us/excel-help/count-the-unique-entries-in-a-column-of-data-HA001044862.aspx

lambertwm,

thanks!

Thanks so much. This manual and the comments (like Tony's one) are absolutely brilliant!

Frans,

Thank you for commenting!

I have 2 columns of data--one is for building numbers and the other is for individuals working in the buildings. I need to sum the number of different/unique individuals within each building, generating a table of the information. What formula would let me do that? So, for example

Column A (Bldg#) Column D (Worker ID)

010 John24

010 Sue01

821 Joe22

010 John24

650 Mary19

650 Gene22

821 Joe22

Results:

Building 010 has 2 people working in it

Building 650 has 2 people working in it

Building 821 has 1 person working in it

I need help with this same problem, different data of course. Would love to know if this is possible. Thanks!

Anne and labraun,

Formula in cell A14:

=INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($A$13:A13,$A$2:$A$9),0,0),0))

Formula in cell B14:

=SUMPRODUCT((A14=$A$2:$A$9)*($D$2:$D$9<>"")*(1/COUNTIFS($A$2:$A$9,$A$2:$A$9&"",$D$2:$D$9,$D$2:$D$9&"")))

Download excel file

Count-unique-distinct-with-criteria.xlsx

Read more:

Count unique distinct values that meet multiple criteria

i need to count unique number in coloured cell

for eg

if there are coloured cell like red yellow green

and in want to know unique number in red cell....

PRASHANT,

Read this post:

Count unique distinct values by cell color

Oscar -- you are a genius -- we needed a solution that did not require array formulas due to an integration with an excel generator from a template (Conga Composer) so we used as you described:

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

[…] solution that worked for us (with limitations) Some Google searching yielded this count distinct formula that does not rely on Excel array formulas: […]

Hi Oscar,

Found very strange situation with Cyrillic texts in List1 that produce not integer result with two formulas - SUMPRODUCT and SUM(1/COUNTIF(List1, List1)).

Result is 6,999999999999990000000000000000, instead of 7.

I failed to find any reason for it. Do you want to send you test file?

Best regards

Todor

[…] The technique described here is used in this popular post: Count unique distinct values […]