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


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))
Counting Unique Values in Column

 

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

Counting Unique Text Values

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

Counting Unique Numeric Values

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

Read More: Count Unique Values Based on Criteria in Another Column


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

Remarking Case-sensitive Unique Values

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

Counting Case-sensitive Unique Values

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

Using UNIQUE Function

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

Read More: SUMPRODUCT Function to Count Unique Values with Criteria


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

Using COUNTIFS Function


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.

Applying Remove Duplicates Option

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

Defining Columns to Remove Duplicates

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

Counting Unique Values


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.

Creating Pivot Table

  • 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.

Defining Location of Pivot Table

  • 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.

Selecting Value Field Settings Option

  • Now, select the Distinct Count option and click on OK from the Value Field Settings… wizard.

Selecting Distinct Value Option

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

Excel Count Unique Values with Pivot Table

Read More: Excel VBA: Count Unique Values in a Column


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.

Openning Visual Basic Editor

  • Then click on Module from the Insert tab.

 Creating a Module for Excel Count Unique Values

  • 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

Generating VBA Function

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

VBA Excel Count Unique Values


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.

Download Practice Workbook

You can download the practice workbook from here.


Conclusion

In this article, I have tried to give a complete overview of Excel count unique values. I hope this article will be helpful for you. For any further questions, please comment below. You can also visit our site for more Excel-related articles.


Frequently Asked Questions

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo