How to count unique values in Excel
In this tutorial, you will learn how to count unique values in Excel with formulas, and how to get an automatic count of distinct values in a pivot table. We will also discuss a number of formula examples for counting unique names, texts, numbers, cased-sensitive unique values, and more.
When working with a large dataset in Excel, you may often need to know how many duplicate and unique values are there. And sometimes, you may want to count only the distinct (different) values.
If you have been visiting this blog on a regular basic, you already know the Excel formula to count duplicates. And today, we are going to explore different ways to count unique values in Excel. But for the sake of clarity, let's define the terms first.
- Unique values - these are the values that appear in the list only once.
- Distinct values - these are all different values in the list, i.e. unique values plus 1st occurrences of duplicate values.
The following screenshot demonstrates the difference:
And now, let's see how you can count unique and distinct values in Excel using formulas and PivotTable features.
How to count unique values in Excel
Here's a common task that all Excel users have to perform once in a while. You have a list of data and you need to find out the number of unique values in that list. How do you do that? Easier than you may think :) Below you will find a few formulas to count unique values of different types.
Count unique values in a column
Supposing you have a column of names in your Excel worksheet, and you need to count unique names in that column. The solution is to use the SUM function in combination with IF and COUNTIF:
Note. This is an array formula, so be sure to press Ctrl + Shift + Enter to complete it. Once you do this, Excel will automatically enclose the formula in {curly braces} like in the screenshot below. In no case should you type the curly braces manually, that won't work.
In this example, we are counting unique names in range A2:A10, so our formula takes the following shape:
=SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))
Further on in this tutorial, we are going to discuss a handful of other formulas to count unique values of different types. And because all those formulas are variations of the basic Excel unique values formula, it makes sense to break down the above formula, so you can fully understand how it works and tweak it for your data. If someone is not interested in technicalities, you can skip right to the next formula example.
How the Excel count unique values formula works
As you see, 3 different functions are used in our unique values formula - SUM, IF and COUNTIF. Looking from the inside out, here's what each function does:
- The COUNTIF function counts how many times each individual value appears in the specified range.
In this example,
COUNTIF(A2:A10,A2:A10)returns the array{1;2;2;1;2;2;2;1;2}. - The IF function evaluates each value in the array returned by COUNTIF, keeps all 1's (unique values), and replaces all other values with zeros.
So, the function
IF(COUNTIF(A2:A10,A2:A10)=1,1,0)becomesIF(1;2;2;1;2;2;2;1;2) = 1,1,0,which turns into the array{1;0;0;1;0;0;0;1;0}where 1 is a unique value and 0 is a duplicate value. - Finally, the SUM function adds up the values in the array returned by IF and outputs the total number of unique values, which is exactly what we wanted.
Count unique text values in Excel
If your Excel list contains both numerical and text values, and you want to count only unique text values, add the ISTEXT function to the array formula discussed above:
=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))
As you know, the Excel ISTEXT function returns TRUE if an evaluated value is text, FALSE otherwise. Since the asterisk (*) works as the AND operator in array formulas, the IF function returns 1 only if a value is both text and unique, 0 otherwise. And after the SUM function adds up all 1's, you will get a count of unique text values in the specified range.
Don't forget to press Ctrl + Shift + Enter to correctly enter the array formula, and you will get a result similar to this:
As you can see in the screenshot above, the formula returns the total number of unique text values, excluding blank cells, numbers, logical values of TRUE and FALSE, and errors.
Count unique numeric values in Excel
To count unique numbers in a list of data, utilize an array formula like we've just used for counting unique text values, with the only difference that you embed ISNUMBER instead of ISTEXT in your unique values formula:
=SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))
Count case-sensitive unique values in Excel
If your table contains case-sensitive data, the easiest way to count unique values would be creating a helper column with the following array formula to identify duplicate and unique items:
=IF(SUM((--EXACT($A$2:$A$10,A2)))=1,"Unique","Dupe")
And then, use a simple COUNTIF function to count unique values:
=COUNTIF(B2:B10, "unique")
Count distinct values in Excel (unique and 1st duplicate occurrences)
To get a count of distinct values in a list, use the following formula:
Remember, it's an array formula, and therefore you should press the Ctrl + Shift + Enter shortcut instead of the usual Enter keystroke.
Alternatively, you can use the SUMPRODUCT function and complete the formula in the usual way by pressing the Enter key:
For example, to count the distinct values in range A2:A10, you can go with either:
=SUM(1/COUNTIF(A2:A10,A2:A10))
Or
=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))
How the Excel distinct formula works
As you already know, we use the COUNTIF function to find out how many times each individual value appears in the specified range. In the above example, the result of the COUNTIF function is the following array: {2;2;3;1;2;2;3;1;3}.
After that, a number of division operations are performed, where each value of the array is used as a divisor with 1 as the dividend. This turns all duplicates values into fractional numbers corresponding to the number of duplicate occurrences. For example, if a value appears 2 times in the list, it generates 2 items in the array with a value of 0.5 (1/2=0.5). And if a value appears 3 times, it produces 3 items in the array with a value of 0.3(3). In our example, the result of 1/COUNTIF(A2:A10,A2:A10)) is the array {0.5;0.5;0.3(3);1;0.5;0.5;0.3(3);1;0.3(3)}.
Doesn't make much sense so far? That's because we haven't applied the SUM / SUMPRODUCT function yet. When one of these functions adds up the values in the array, the sum of all fractional numbers for each individual item always yields 1, no matter how many occurrences of that item exist in the list. And because all unique values appear in the array as 1's (1/1=1), the final result returned by the formula is the total number of all different values in the list.
Formulas to count distinct values of different types
As is the case with counting unique values in Excel, you can use variations of the basic Excel count distinct formula to handle specific value types such as numbers, text, and case-sensitive values.
Please remember that all of the below formulas are array formulas and require pressing Ctrl + Shift + Enter.
Count distinct values ignoring empty cells
If a column where you want to count distinct values might contain blank cells, you should add an IF function that will check the specified range for blanks (the basic Excel distinct formula discussed above would return the #DIV/0 error in this case):
For example, to count distinct values in range A2:A10, use the following array formula:
=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))
Formula to count distinct text values
To count distinct text values in a column, we'll be using the same approach that we've just used to exclude empty cells.
As you can easily guess, we will simply embed the ISTEXT function into our Excel count distinct formula:
And here's a real-life formula example:
=SUM(IF(ISTEXT(A2:A10),1/COUNTIF(A2:A10, A2:A10),""))
Formula to count distinct numbers
To count distinct numeric values (numbers, dates and times), use the ISNUMBER function:
For example, to count all different numbers in range A2:A10, use the following formula:
=SUM(IF(ISNUMBER(A2:A10),1/COUNTIF(A2:A10, A2:A10),""))
Count case-sensitive distinct values in Excel
Similarly to counting case-sensitive unique values, the easiest way to count case-sensitive distinct values is to add a helper column with the array formula that identifies unique values including first duplicate occurrences. The formula is basically the same as the one we used to count case-sensitive unique values, with one small change in a cell reference that makes a great difference:
=IF(SUM((--EXACT($A$2:$A2,$A2)))=1,"Distinct","")
As you remember, all array formulas in Excel require pressing Ctrl + Shift + Enter.
After the above formula is finished, you can count "distinct" values with a usual COUNTIF formula like this:
=COUNTIF(B2:B10, "distinct")
If there is no way you can add a helper column to your worksheet, you can use the following complex array formula to count case-sensitive distinct values without creating an additional column:
=SUM(IFERROR(1/IF($A$2:$A$10<>"", FREQUENCY(IF(EXACT($A$2:$A$10, TRANSPOSE($A$2:$A$10)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), ""), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0), 0))
Count unique and distinct rows in Excel
Counting unique / distinct rows in Excel is akin to counting unique and distinct values, with the only difference that you use the COUNTIFS function instead of COUNTIF, which lets you specify several columns to check for unique values.
For example, to count unique or distinct names based on the values in columns A (First Name) and B (Last Name), use one of the following formulas:
Formula to count unique rows:
=SUM(IF(COUNTIFS(A2:A10,A2:A10, B2:B10,B2:B10)=1,1,0))
Formula to count distinct rows:
=SUM(1/COUNTIFS(A2:A10,A2:A10,B2:B10,B2:B10))
Naturally, you are not limited to counting unique rows based only on two columns, the Excel COUNTIFS function can process up to 127 range/criteria pairs.
Count distinct values in Excel using a PivotTable
The latest versions of Excel 2013 and Excel 2016 have a special feature that allows counting distinct values automatically in a pivot table. The following screenshot gives an idea of how the Excel Distinct Count looks like:
To create a pivot table with the distinct count for a certain column, perform the following steps.
- Select the data to be included in a pivot table, switch to the Insert tab, Tables group, and click the PivotTable button.
- In the Create PivotTable dialog, choose whether to place your pivot table in a new or existing worksheet, and be sure to select the Add this data to the Data Model checkbox.
- When your pivot table opens, arrange the Rows, Columns and Values areas the way you want. If you don't have much experience with Excel pivot tables, the following detailed guidelines may prove helpful: Creating a PivotTable in Excel.
- Move the field whose distinct count you want to calculate (Item field in this example) to the Values area, click on it, and select Field Value Settings… from the drop-down menu:
- The Value Field Settings dialog window will open, you scroll down to Distinct Count, which is the very last option in the list, select it and click OK.
You can also give a custom name to your Distinct Count if you want to.
Done! The newly created pivot table will display the distinct count like shown in the very first screenshot in this section.
This is how you count distinct and unique values in Excel. If someone wants to have a closer look at the formulas discussed in this tutorial, you are welcome to download the sample Excel Count Unique workbook.
I thank you for reading and hope to see you again next week. In the next article, we are going to discuss various ways to find, filter, extract and highlights unique values in Excel. Please stay tuned!
