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 - earlier Excel versions
- Count unique values
1.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.
1.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.
1.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))))
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:
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
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
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
The SUM function in Excel allows you to add values, the function returns the sum in the cell it is […]
2.2 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
The image above shows a table with 3 columns containing random data. It is quite complicated trying to manually count […]
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)
2.3 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
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.
Get excel *.xlsx file
Count unique distinct values case sensitive.xlsx
2.4 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
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
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:
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:
Recommended articles
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.
Get 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
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:
Watch a video where I explain the formula
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.
Get excel *.xlsx file
Case sensitive category
The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
Count unique distinct values category
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
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 Double press with left mouse […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
29 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.
It is the same theme in my previous comment. The ability to count unique entries with blank cells in the range.
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&"")))
Get the 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 […]
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,
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