# 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 - Excel 365
- Count unique distinct values in a cell range with blanks - Excel 365
- Count unique distinct values in multiple nonadjacent cell ranges - Excel 365
- Count unique
**distinct**values - Count unique distinct values in a cell range with blanks
- Count unique
**distinct**values (case sensitive) - Count unique distinct values in two columns
- Count unique distinct months
- Count digits and ignore duplicates
- Count unique distinct values in a large dataset - UDF
- Count unique values
- Count unique values (case sensitive)
- Count unique distinct values within same week, month or year

**Count unique distinct values - Excel 365**

**Count unique distinct values - earlier Excel versions**

**Count unique distinct values - User defined function**

**Count unique values**

**All Excel versions**

## 1 Count unique distinct values - Excel 365

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:

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

## 2 Count unique distinct values in a cell range with 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:

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

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

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:

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

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

Recommended articles

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.

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

### 4.1 How the formula works

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

**Step 1 - Count each value**

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

Counts the number of cells that meet a specific condition.

**Step 2 - Divide 1 with array**

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

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

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

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

Recommended articles

Table of Contents Count unique distinct records Count records with possible blank rows in data set How to count blank […]

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

Recommended articles

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

**Get excel example file**

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

(Excel 97-2003 Workbook *.xls)

**6. 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]

Recommended articles

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

**How to enter an array formula**

- Select cell C2
- Press with left mouse button on in formula bar
- Paste above array formula
- Press and hold Ctrl + Shift
- Press Enter

### 6.1 Explaining array formula in cell D4

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

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

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

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

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

**Get excel *.xlsx file**

Count unique distinct values case sensitive.xlsx

## 7. Count unique distinct values in two columns

Formula in C12:

### How to create an array formula

- Double press with left mouse button on cell C12
- Paste above formula
- Press and hold Ctrl + Shift
- Press Enter

### Explaining formula in cell C12

**Step 1 - Count values in cell range B3:B8**

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

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

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

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

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

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

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

## 8. Count unique distinct months

The formula in cell D18 counts unique distinct months in cell range B3:B16.

Formula in D18:

### Explaining formula in cell D18

#### Step 1 - Convert dates

The DATE function changes each date to the first in the given month and year.

DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1)

becomes

DATE(YEAR({40059; 40228; 39946; 39919; 39891; 39921; 40106; 40243; 40144; 40202; 40179; 39880; 40171; 40019}), MONTH({40059; 40228; 39946; 39919; 39891; 39921; 40106; 40243; 40144; 40202; 40179; 39880; 40171; 40019}), 1)

becomes

DATE({2009; 2010; 2009; 2009; 2009; 2009; 2009; 2010; 2009; 2010; 2010; 2009; 2009; 2009}, {9; 2; 5; 4; 3; 4; 10; 3; 11; 1; 1; 3; 12; 7}, 1)

and returns

{40057; 40210; 39934; 39904; 39873; 39904; 40087; 40238; 40118; 40179; 40179; 39873; 40148; 39995}

#### Step 2 - Count frequency of each number

The FREQUENCY function returns an array of numbers representing how many times the number occurs in the list.

FREQUENCY(DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1),DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1))

becomes

FREQUENCY({40057; 40210; 39934; 39904; 39873; 39904; 40087; 40238; 40118; 40179; 40179; 39873; 40148; 39995},{40057; 40210; 39934; 39904; 39873; 39904; 40087; 40238; 40118; 40179; 40179; 39873; 40148; 39995})

and returns

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

#### Step 3 - Check if value is larger than 0 (zero)

We know a value is unique distinct if it is larger than 0 (zero).

(FREQUENCY(DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1),DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1))>0

becomes

{1;1;1;2;2;0;1;1;1;2;0;0;1;1;0}>0

and returns

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

#### Step 4 - Convert boolean values

We must first convert the boolean values in order to sum the values, multiply with 1 to create their numerical equivalents.

(FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1))>0)*1

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE}*1

and returns

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

#### Step 5 - Sum values

Use the SUMPRODUCT function to add the numbers and return a total.

SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1),DATE(YEAR($B$3:$B$16),MONTH($B$3:$B$16),1))>0)*1)

becomes

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

and returns 11 in cell D18.

### Get Excel *.xlsx

Count unique distinct months.xlsx

## 9. Count digits and ignore duplicates

**Question:**

I have a question that I can’t seem to find an answer to:

I want to make a full count of digits 0 to 9 while ignoring duplicates in any line

B C D E

5 5 5 5

8 6 7 4

6 2 8 7

7 7 1 6

5 6 6 2For example, with the digits shown above, my results for the count will be:

0=0; 1=1; 2=2; 3=0; 4=1; 5=5; 6=4; 7=3; 8=2; 9=0

The formula below does not work for the “5” count since it counts all the occurrences:

=COUNTIF($B$1:$E$5,5)

### Answer:

I can't achieve the desired result for number 5. I calculated any line as "in any row" and "in any column". I guess the desired result for number 5 is a typo.

**Array formula in cell B9:**

Copy cell B9 and paste down as far as needed.

**Array formula in cell C9:**

Copy cell C9 and paste down as far as needed.

### How the formula works in cell B9

*Step 1 - Find digit in array*

=SUM(IF(FREQUENCY(IF(**A9=$B$1:$E$5**, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, ""), ROW($1:$5))>0, 1, 0))

A9=$B$1:$E$5

becomes

0={5, 5, 5, 5;8, 6, 7, 4;6, 2, 8, 7;7, 7, 1, 6;5, 6, 6, 2}

becomes

{FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}

*Step 2 - Convert boolean array to row numbers*

=SUM(IF(FREQUENCY(**IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, "")**, ROW($1:$5))>0, 1, 0))

IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, "")

becomes

IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, {1, 2, 3, 4}, "")

becomes

{"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", ""}

*Step 3 - Count row numbers*

=SUM(IF(**FREQUENCY(IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, ""), ROW($1:$5))**>0, 1, 0))

FREQUENCY(data_array, bins_array)*
*Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array

*.*

FREQUENCY(IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, ""), ROW($1:$5))

becomes

FREQUENCY({"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", ""}, {1; 2; 3; 4; 5})

becomes

{0; 0; 0; 0; 0}

*Step 4 - Count numbers larger than zero*

=SUM(IF(FREQUENCY(IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, ""), ROW($1:$5))>0, 1, 0))

becomes

=SUM(IF({0; 0; 0; 0; 0}>0, 1, 0))

becomes

=SUM({0; 0; 0; 0; 0}) and returns 0 (zero).

## 10. Count unique distinct values in a large dataset - UDF

This article describes how to count unique distinct values in list. What is a unique distinct list? Merge all duplicates to one distinct value and you have created a unique distinct list.

Formulas are sometimes too slow if you work with a lot of data, this article demonstrates a user defined function you can use, however, I highly recommend using a pivot table for this task:

Count unique distinct values using a pivot table

Formula in cell H2:

### Excel user defined function

Public Function CountUniqueValues(rng As Variant) As Variant Dim Test As New Collection Dim Value As Variant rng = rng.Value On Error Resume Next For Each Value In rng If Len(Value) > 0 Then Test.Add Value, CStr(Value) Next Value On Error GoTo 0 CountUniqueValues = Test.Count End Function

### How to add the user defined function to your workbook

1. Press Alt-F11 to open the visual basic editor

2. Press with left mouse button on Module on the Insert menu

3. Copy and paste the above user defined function

4. Exit visual basic editor

5. Select a sheet

6. Select a cell range

7. Type =CountUniqueValues(A1:F3000) into formula bar and press *ENTER*

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

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

Counts the number of cells that meet a specific condition.

**Step 2 - Check each value is equal to 1**

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

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

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

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

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

### 12.1 Explaining array formula in cell D3

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

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

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

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

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

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

### Get excel *.xlsx file

Count unique values case sensitive.xlsx

## 13. Count unique distinct values within same week, month or year

The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 has two unique distinct items: 1150 and 1131.

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

**Formula in B3:**

**Array formula in E3:**

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

Copy cell E3 and paste it down as far as needed.

Item is named range pointing to cell range D3:D11.

### Explaining array formula in cell E3

=SUM(--((FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))>0))

**Step 1 - Filter records in date range**

=SUM(--((FREQUENCY(**IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, "")**, Item))>0))

IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, "")

becomes

IF("2010-2"={"2010-2";"2010-2";"2010-2";"2010-2";"2011-3";"2010-3";"2010-3";"2010-3";"2010-3"}, Item, "")

becomes

IF({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}, {1150; 1150; 1131; 1131; 1126; 1151; 1150; 1131; 1131}, "")

and returns

{1150;1150;1131;1131;"";"";"";"";""}

**Step 2 - Calculate frequency**

=SUM(--((**FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))**>0))

FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))

becomes

FREQUENCY({1150;1150;1131;1131;"";"";"";"";""}, {1150; 1150; 1131; 1131; 1126; 1151; 1150; 1131; 1131}))

and returns

{2;0;2;0;0;0;0;0;0;0}

**Step 3 - Count and sum values larger than 0 (zero)**

=SUM(--((FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11, Item, ""), Item))>0))

becomes

=SUM(--(({2;0;2;0;0;0;0;0;0;0})>0))

becomes

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

becomes

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

and returns 2 in cell E3.

### Get Excel *.xlsx file

Count-unique-occurences-within-same-week-month-year.xlsx

### Count unique distinct values within same month

**Array formula in E16:**

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

Copy cell E16 and paste it down as far as needed.

### Count unique distinct values within same year

**Array formula in E29:**

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

Copy cell E29 and paste it down as far as needed.

### Get excel sample file

Count-unique-occurences-within-same-week-month-year.xls

### Case sensitive category

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

### Count unique distinct values category

This post demonstrates how to build formulas that counts unique distinct values based on criteria. The image above demonstrates an […]

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

### Excel categories

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

Paste image link to your comment.

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

Oscar,

You don't need the countif part, you could directly be constructing the Frequency structure, using the name range "Item"

=SUM(--(FREQUENCY(IF(YEAR(C3)&"-"&B3=YEAR($C$3:$C$11)&"-"&$B$3:$B$11,Item,""),(Item))>0))

Or am I missing something here

Yes, you are right.

But if "Item" is text (not numbers), frequency won´t be able to "count" them. COUNTIF($D$3:$D$11, "<"&$D$3:$D$11) converts all possible text values to numbers. In my example I use only numbers so my solution might seem strange. I wanted to create a more general solution if people use "Item" numbers like this: A111, A112 and so on. Thanks your contribution! /Oscar

And I now see the light! Thanks Oscar!

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

How then would I run or fit in the countif, if I had text that I had to look for distinct values. I have text values in item column. David

I know a formulas

1*30*(5+7)/2*(0.90+0.70+0.60)/3 =

Please send a formula in my email

January 17th, 2012 at 7:26 pm

I know a formulas

1*30*(5+7)/2*(0.90+0.70+0.60)/3 =

Please send a formula in my email

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!

Hi, hope you can help me.

If I have the below data:

A B

1 1233 MEL

2 4562 MEL

3 1233 MEL

4 7625 SYD

5 7352 SYD

6 4562 MEL

7 2447 SYD

How do I find out how many unique codes there are in column A from “MEL” in B? I’ve tried multiple formulas but they keep coming up as zero, whereas in this case the answer should be 2.

Thanks!

Bet,

read this post:

Count unique distinct values that meet multiple criteria in excel

Hi,

How would you count three columns of unique users? I have three worksheets of people who have received training, some people are on multiple worksheets and multiple times. I just want to find how many unique users there are in total.

Angelica,

Try this array formula:

=SUM(IF(List1<>"",1/COUNTIF(List1,List1),0))+SUM(IF((COUNTIF(List1,List2)=0)*(List2<>""),1/COUNTIF(List2,List2),0))

It counts unique values from two columns with blanks. List1 and List2 are named ranges.

Three ranges are more complicated.

Thanks Oscar, I tried the formula but it didn't work. I think because of the "" part. I got a #Value! error.

I'm not that familiar with "", so I tried taking out , but that didn't work, then I tried taking out "". That didn't work either.

I already named my lists Basics, Advanced and Contribute since I have three columns in three separate tabs. Basics is a column from B3 to B301, Advanced is from B3 to 101, Contribute is from B3 to B101.

The formula I tried was:

=SUM(IF(Basics"",1/COUNTIF(Basics,Basics),0))+SUM(IF((COUNTIF(Basics,Contribute)=0)*(Contribute""),1/COUNTIF(Contribute,Contribute),0))

Any advice would be appreciated!

Those right and left carets aren't showing up in my post. But I did use them.

Angelica,

Those right and left carets aren't showing up in my post. But I did use them.That is how you recognize an array formula in excel because it is enclosed in curly brackets { }.

There are instructions in this post about how to create array formulas.

Also I should mention that the list grows weekly and the Lists I have created have several blank cells which is why I couldn't get your example to work for my problem.

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&"")))

Get the Excel file

Count-unique-distinct-with-criteria.xlsx

Read more:

Count unique distinct values that meet multiple criteria

how about unique distinct values within same day? looking forward to your response.

beginner,

Array formula in cell F4:

Get the Excel *.xlsx file

count-unique-distinct-values-within-the-same-day.xlsx

thank you, oscar.

Hello,

I have data in the following format:

Longitude Latitude Magnitude

72.87 33.73 7.6

69.45 37.15 6.9

69 34.5 5.3

69.1 34.5 7.2

71.8 34.8 5.3

75.5 33.5 7.6

75 34 6.9

73.23 33.37 6.9

77 35 6.1

76 34 5.3

72.3 33.9 6.1

80 30 7.5

75 34 6.9

80 31.3 7

75 34 7

76 34 5.3

75 34 6.7

75 34 6.5

80 30 6.1

79 31.5 7.5

79 30 6.9

I want to count number of magnitude values in column three in 0.1*0.1 latitude and longitude e.g (Between 76.1 long and 34.1 Lat).

Can somebody please help me? I shall be thankful

Muhammad Waseem

Muhammad Waseem,

I am not sure I understand, you want to count longitude values less than 76.1 and latitude values more than 34.1?

Dear Oscar,

Thank you very much for your response. Your comment is helpful. Actually, I am interested in counting in numbers between 34.1 and 34.2, 76.1 and 76.2: 34.2-34.3, 76.2-76.3 and so on.

Thank you

Muhammad Waseem

Muhammad Waseem,

Get the Excel *.xlsx file

count-long-and-latv2.xlsx

Dear Mr. Oscar,

Thank you very much for the help and for the file.

Regards,

Muhammad Waseem

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

How Can I also count "A" and "a" two different distinct values ?

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

Hi Oscar, hope you can help me.

I have two columns, 'A. Call Subject' and 'B. Issue'.

I am trying to report the number of Issues that are related to Call Subject.

The problem is that text in 'B. Issue' can be "other" for many 'A. Call Subject'.

An example, 'A. Call Subject' cells have the following text - "Sales", "Technical", "Other" - and 'B. Issues' has texts of - "New Product" and "Other" -.

How can I count the number of "other" from 'B. Issues' that are related to 'A. Call Subject' of Sales" or Technical.

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

Hi Oscar,

Assume column 1 is a list of people's names; column 2 is a list of fruits, veg and drinks that they sell; and column 3 is whether the items in column 2 fall into the category of fruit, veg or drinks.

I am trying to work out a formula that would simply count the number of people who sell fruit; the number of people who sell veg and the number of people who sell drinks. I am sure there is probably some IFERROR array formula that can calculate this but unfortunately I am still a novice at this and can't quite get my head round exactly how it works. Any help you can give me on this will be GREATLY appreciated.

Many thanks,

Steve

https://postimg.cc/CBKFY3J8

Hi Steve

This article explains how to count values based on a condition using a formula:

https://www.get-digital-help.com/2017/08/28/count-unique-distinct-values-based-on-a-condition/

This article explains how to count values based on a condition using a pivot table:

https://www.get-digital-help.com/2016/07/04/excel-pivot-tables/#countu

Hi Mr. Oscar - thank you for a great and very clear article! I have used your suggestion successfully.

Question: Is there a way to achieve the same result (a count of unique values) using the Excel UNIQUE function instead of this approach? My 2 columns of data includes both text strings and real numbers, so would need to handle those.

Thank you again! (Also love your profile!!)

Hi,

Thanks for formula and tutorial.

The text and non-text (numeric) unique and distinct values in the column are quickly listed separately using VBA macro.

Image of macro : https://imgur.com/7pyXmvL

Source