# Excel Count Unique Values (A Complete Overview)

Get FREE Advanced Excel Exercises with Solutions!

This article gives a complete overview on Excel count unique values. Here, I will try to explain counting unique values as well as distinct values in Excel.

We like to have our data sorted and decorated. For this, we often feel the necessity of counting the unique and repetitive work.

There is a little difference between unique and distinct terms. The unique values are those which have no repetition. On the other hand, the distinct values ar those which have repetition but all of the repetition counts as One. The procedures of counting both the unique and distinct values are described in the following sections.

## Excel Count Unique Values: 4 Unique Cases

### 1. Counting Unique Values in Column (General Formula)

• In order to find out the unique values in a certain column, use the following value combined with the IF, COUNTIF, and SUMÂ functions.
`=SUM(IF(COUNTIF(C6:C15,C6:C15)=1,1,0))`

Â

Formula Breakdown

COUNTIF(C6:C15,C6:C15)=1 â€”> checks if there is any repetitive value.
Output: {TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

IF(COUNTIF(C6:C15,C6:C15)=1,1,0) â€”> indicates the unique values as 1 and others as 0.
Output: {1;1;0;1;0;0;0;0;1;0}

SUM(IF(COUNTIF(C6:C15,C6:C15)=1,1,0)) â€”> calculates the total of unique numbers.
Output: 4

### 2. Count Unique Text Values in Excel

• If you want to count the text values only in a certain range, apply the following formula combined with the ISTEXTÂ function.
`=SUM(IF(ISTEXT(C6:C15)*COUNTIF(C6:C15,C6:C15)=1,1,0))`

Formula Breakdown

ISTEXT(C6:C15) â€”> checks whether the value is text or not.
Output: {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

COUNTIF(C6:C15,C6:C15)=1 â€”> checks if there is any repetitive value.
Output: {TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

IF(ISTEXT(C6:C15)*COUNTIF(C6:C15,C6:C15)=1,1,0) â€”> indicates the unique text values as 1 and others as 0.
Output: {1;1;0;1;0;0;0;0;1;0}

SUM(IF(ISTEXT(C6:C15)*COUNTIF(C6:C15,C6:C15)=1,1,0)) â€”> calculates the total of unique text values.
Output: 4

### 3. Count Unique Numeric Values in Excel

`=SUM(IF(ISNUMBER(F5:F14)*COUNTIF(F5:F14,F5:F14)=1,1,0))`

Formula Breakdown

ISNUMBER(F5:F14) â€”> checks whether the value is number or not.
Output: {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

COUNTIF(F5:F14,F5:F14)=1 â€”> checks if there is any repetitive value.
Output: {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

IF(ISNUMBER(F5:F14)*COUNTIF(F5:F14,F5:F14)=1,1,0) â€”> indicates the unique number values as 1 and others as 0.
Output: {0;0;0;0;0;0;0;0;1;0}

SUM(IF(ISTEXT(C6:C15)*COUNTIF(C6:C15,C6:C15)=1,1,0)) â€”> calculates the total of unique text values.
Output: 1

### 4. Count Case-Sensitive Unique Values in Excel

• In order to count the case-sensitive unique values, find out the unique values first in the Remarks column. You can do that by inserting the following formula combined with the EXACTÂ function.
`=IF(SUM((--EXACT(\$C\$6:\$C\$15,C6)))=1,"One Time","Multiple Times")`

• Then, apply the following formula with COUNTIF to count those unique values.
`=COUNTIF(F6:F15, "One Time")`

Note
The EXACT function here refers to case-sensitive as it considers the upper case and lower case of a letter differently.

## Count Distinct Values in Excel: 5 Different Approaches

### 1. Using UNIQUE Function

• We can use a combined formula with the UNIQUE and COUNTA functions to count the distinct values.
`=COUNTA(UNIQUE(C6:C15))`

TheÂ UNIQUEÂ function here returns all the unique values only from the range. Then theÂ COUNTAÂ function counts the number of them.

### 2. Using COUNTIFS Function to Count Distinct Values

• The combined use of the SUM and inverse COUNTIFS functions can be used to find the distinct values.
`=SUM(1/COUNTIFS(C6:C15,C6:C15))`

### 3. Removing Duplicates to Get Only Distinct Values

• In order to remove the duplicate values, select the defined area and go to the Data tab.
• Next, click on Remove Duplicates from the ribbon.

• A Remove Duplicates wizard will appear. Select the column based on what you want to remove the entire rows and click OK.

• We will have the count of duplicate and unique values on the screen.

### 4. Using Pivot Table to Count Distinct Values from Column

• With the help of the Pivot Table feature, we can count the unique values too. For this, select the entire range and go to the InsertÂ tab.
• After that, click on From Table/Range from the PivotTableÂ option.

• A PivotTable from table or range wizard will pop up.
• Then, select the Existing Worksheet option and define the cell in the Location section where you want to have the count of the distinct values.
• Finally, check the Add this data to the Data Model box and click OK.

• Select a column header (i.e. Player) from the Range option and drag it to the Values section from PivotTable Fields.
• Now, left-click on the mouse placing the cursor on Count of Player, and pick the Value Field Settingsâ€¦Â option.

• Now, select the Distinct Count option and click on OK from the Value Field Settingsâ€¦Â wizard.

• We will have the number of distinct values in the defined cell.

### 5. Use of VBA

• The smartest way to count the distinct values is using VBA. For this, go to Visual Basic from the DeveloperÂ tab.

• Then click on Module from the InsertÂ tab.

• Now write the following code to create a function.
``````Function DistinctValueCount(Rng As Range) As Integer
Application.Volatile

Dim i As Variant
Dim DistinctPlayer As New Collection

On Error Resume Next
For Each i In Rng
Â Â Â  If Not (IsEmpty(i)) Then
Â Â Â Â Â Â Â  DistinctPlayer.Add i, CStr(i)
Â Â Â  End If
Next i

DistinctValueCount = DistinctPlayer.Count
End Function``````

• Now, apply the following formula with the created function to have the count of the distinct numbers.
`=DistintValueCount(C6:C15)`

## Things to Remember

• In a case-sensitive situation, the EXACT function considers the upper case and lower case of a letter differently.
• The count of unique and distinct values will be different.
• Counting unique values with the removing duplicates procedure will show the result in a message box.

## Conclusion

1. Difference between unique and distinct values?

The unique values are those which have no repetition. On the other hand, the distinct values are those which have repetition but all of the repetition counts as One.

2. Is there a built-in Excel function to count distinct values?

Yes, there is a built-in function named UNIQUE to count the distinct values.

3. What are some practical use cases for counting unique values in Excel?

Data analysis, data cleansing, market research, inventory management, etc are some practical use cases for counting unique values in Excel.

## Excel Count Unique Values: Knowledge Hub

<< Go Back to Unique ValuesÂ | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF