Normally, in Excel, the unique values are the values that appear only once in the list without any duplications, and distinct values are all the different values (unique values + 1st duplicate occurrences). When working on a large dataset, you may need to count the number of unique and distinct values among duplicates from a list of cells as below screenshot shown. This tutorial will introduce some quick tricks for counting the unique and distinct values in Excel.
Count distinct values (unique and 1st duplicate occurrences) in Excel
This section will talk about some formula examples for counting the number of unique values, including texts and numbers in a list.
Supposing, I have a list of names which contains some duplicate names within the name list. Now, I need to get the number of unique names only (which are filled with yellow color) as below screenshot shown:
To solve this issue, the following array formula can do you a favor:
Step 1: Input formula
Enter or copy the below formula into a blank cell where you want to output the result:
=SUM(IF(COUNTIF(A2:A12,A2:A12)=1,1,0))
Step 2: Press Ctrl + Shift + Enter keys to get the correct result:
Tips:
=SUM(IF(ISTEXT(A2:A12)*COUNTIF(A2:A12,A2:A12)=1,1,0))
=SUM(IF(ISNUMBER(A2:A12)*COUNTIF(A2:A12,A2:A12)=1,1,0))
It may be painful for you to remember the formulas when you need to apply them next time. But, if you have Kutools for Excel, with its Count Unique Values option of Formulas Helper, you can get the result with only a few clicks. See below demo:
If you are using Excel 365 or Excel 2021, there is a new UNIQUE function which can help you to create more simper formulas to count unique values in a set of data.
For example, to count the number of unique names in the range of A2:A12, please enter the following formula:
Step 1: Copy or enter the below formula
=IFERROR(ROWS(UNIQUE(A2:A12,,TRUE)), 0)
Step 2: Press Enter key to get the result:
Tips:
=SUM(IF(COUNTIF(A2:A12,A2:A12)=1,1,0))
To count the different values (unique values and 1st duplicate occurrences) in a list of cells, here, I will introduce other formulas to achieve this task.
In Excel, you can apply any one of below formulas to return the number of distinct values.
Step 1: Enter any one of the below formulas
Formula 1: After inputting the formula, press Enter key.
=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))
Formula 2: After inputting the formula, press Ctrl + Shift + Enter keys.
=SUM(1/COUNTIF(A2:A12,A2:A12))
Result:
Tips:
=SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12,A2:A12&""))
=SUM(IF(A2:A12<>"",1/COUNTIF(A2:A12, A2:A12), 0))
=SUM(IF(ISTEXT(A2:A12),1/COUNTIF(A2:A12, A2:A12),""))
=SUM(IF(ISNUMBER(A2:A12),1/COUNTIF(A2:A12, A2:A12),""))
If you need to apply the formulas frequently in your workbook, it may be painful for you to remember the formulas when you need to apply them next time. But, if you have Kutools for Excel, with its Count cells with unique values (include the first duplicate) option of Formulas Helper, you can get the result with only several clicks. See below demo:
In Excel, the PivotTable also can help to get the number of distinct values from a list of data, please do with the following steps:
Step 1: Create a pivot table
Step 2: Arrange the field and choose Distinct Count option
Result:
Now, the created pivot table will display the distinct count of the data list as below screenshot shown:
Tips:
In Excel 365 or Excel 2021, you can use the new UNIQUE function with the normal COUNTA function to create an easy formula.
Please copy or enter the below formula into a blank cell, and press Enter key to get the result:
=COUNTA(UNIQUE(A2:A12))
Tips:
=COUNTA(UNIQUE(FILTER(A2:A12, A2:A12<>"")))