How to Count Unique Values in Excel

Is there a formula to count unique values in Excel?

Yes, you can use a formula to count unique values in Excel. One common way to do this is by using the combination of the COUNTIF and SUM functions along with an array formula. Here’s how you can do it:

What are Unique Values in Excel?

Unique Values in Excel appear in a list of items that occur only once. The main aim of counting unique an distinct values is to separate them from the duplicates of the list in Excel.

How we can Count distinct values in multiple columns simultaneously in Excel?

We can count distinct values in multiple columns with the help of the SUM and COUNTIFS functions together.

• Step 1: Select any random cell where you want the result to display.
• Step 2: Use the Formula “=SUM(1/COUNTIFS(range1,range1, range 2,range2…)” where ranges are the cells in which you want to count the distinct values.
• Step 3: Press “CTRL+SHIFT+ENTER” because of an array formula.

What are the distinct values in Excel?

Distinct values in Excel are the unique values that appear only once in a range of cells. For example, distinct values from the Range {A, B, B, C, C, D}  are {A, B, C, D}.

Explain the formula “=SUM(1/COUNTIFS(B5:B14,B5:B14)”.

The COUNTIFS function is used to see how many times a value appears in the list. This will give you a fractional value that will add up to 1 for each distinct value in the list after inverting this. 

And SUM function then adds all the fractions up and the total is the number of distinct values in the list.  

Is there any In-built function to count the distinct values in Excel without using any formula?

There is no Inbuilt function in Excel to count Distinct values without using formulas. The alternative way is to use the “ Remove Duplicates” feature, This will help you to remove duplicates and give you the Count of unique values.  

Count Unique values in Excel is a feature that can help you to find the values that occur only once in the huge list of Excel. We often need to report the Unique number of customers purchased, the number of products in our stock, List of regions our business covered.  

In this article, we explain how to count unique values in an Excel column.

Before moving ahead you should know the difference between unique values and distinct values.

• Unique Values: These are the values that occur only once. Unique Values from the list {1,2,3,1,4,2,7,5,7} are {3,4,5}.
• Distinct Values: These are the Values that are different. Distinct values from the list are {1,2,3,1,4,2,7,5,7} are {1,2,3,4,5,7}.