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 unique values in Excel

• Count unique values with formulas
• Count unique values with only few clicks by Kutools for Excel
• Count unique values in Excel 365/2021 with formulas

Count distinct values (unique and 1st duplicate occurrences) in Excel

• Count distinct values with formulas
• Count distinct values with only few clicks by Kutools for Excel
• Count distinct values with PivotTable
• Count distinct values in Excel 365/2021 with formulas

Count unique values in Excel

This section will talk about some formula examples for counting the number of unique values, including texts and numbers in a list.

Count unique values with formulas

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:

Count unique values with few clicks by Kutools for Excel

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:

1. Click a cell to output the result;
2. Enable this feature by clicking Kutools > Formula Helper > Formula Helper > Statistical > Count unique values;
3. Select the list of data > OK.
   

Count unique values in Excel 365/2021 with formulas

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:

Count distinct values (unique and 1st duplicate occurrences) in Excel

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.

Count distinct values with formulas

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:

Count distinct values with a few clicks by Kutools for Excel

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:

1. Click a cell to output the result;
2. Enable this feature by clicking Kutools > Formula Helper > Statistical > Count unique cells with unique values (include the first duplicate);
3. Select the list of data > OK.
   

Count distinct values with PivotTable

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

1. Select the data list, and then click Insert > PivotTable from the ribbon, see screenshot:
   
2. In the popped out PivotTable from table or range dialog box:
   (1). Choose a new worksheet or existing worksheet where you want to place the pivot table;
   (2). Then check Add this data to the Data Model checkbox.
   (3). Then click OK button.
   

Step 2: Arrange the field and choose Distinct Count option

1.  A PivotTable Fields pane is displayed, drag the Names field to Values area, see below demo:
   
2. Then, click on the Count of Names drop down, and choose Value Field Settings, see screenshot:
   
3. And then, a Value Field Settings dialog box will open, select Distinct Count from the list box under the Summarize Value By tab, and click OK button, see screenshot:
   

Result:

Now, the created pivot table will display the distinct count of the data list as below screenshot shown:

Count distinct values in Excel 365/2021 with formulas

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

Related Articles: