## Count unique distinct values in a column in excel

**Question:**

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

**Answer:**

**Table of Contents**

- Count unique
**distinct**values (case in-sensitive) - Count unique values (case in-sensitive)
- 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

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

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

**EDIT: Alternative array formula in C1:**

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

*And another*

*alternative array formula*

*in C1:*

**Alternative array formula (No array formula!!)**

**If cell range also contains possible blank cells:**

*+ CTRL + SHIFT + ENTER*

### Alternative array formula (possible blank cells)

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

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 fil****e**

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

- Select cell C2
- Click in formula bar
- Paste above array formula
- Press and hold Ctrl + Shift
- 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**

### 26 Responses to “Count unique distinct values in a column in excel”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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 http://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