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
- To count the unique numbers only, use the following formula which includes the ISNUMBERÂ function.
=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
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")
- Then, apply the following formula with COUNTIF to count those unique values.
=COUNTIF(F6:F15, "One Time")
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.
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))
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.
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.
- 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.
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
