Counting Unique and Distinct Values in an Excel Sheet: A Simple Guide

The terms unique and distinct values are quite common in Microsoft Excel. You may often come across the word unique values when working with large datasheets in Excel. Individual values are the ones that appear in the list only once, and distinct values are all the different values in the list. In this post, we will see how you can count Unique benefits in an Excel sheet.

Counting Unique Values in a Column

If you need to count unique values in a column, you use the SUM function along with IF and COUNTIF like this:

     SUM(IF(COUNTIF(range,range)=1,1,0))

Since this is an array formula, make sure that you press Ctrl + Shift + Enter to complete the task, after which Excel will automatically enclose the formula in curly braces. Other formulas used to count unique values are all variations of the above formula. Hence, it would be better if you fully understand how it works.

How Does the Formula Work?

The formula to count Unique in Excel sheet uses three different functions: SUM, IF, and COUNTIF. The COUNTIF function specifies how often each value appears in the selected range. The IF function is used to evaluate each value in the array returned by COUNTIF. it keeps all Unique values and replaces all other values with zeros. The SUM function is used to add up the values in the array returned by IF. It also outputs the total number of unique values.

Counting Unique Text Values in Excel

If an Excel list contains numerical and text values and you want to count only the unique text values, you need to add the ISTEXT function to the above formula. This will be:

  =SUM(IF(ISTEXT(range)*COUNTIF(range,range)=1,1,0))

If an evaluated value is text, the Excel ISTEXT function will return TRUE and False if otherwise. The asterisk works as the AND operator in the array formula. This means the IF function will return one only if a value is unique and text and 0 if otherwise. 

After the SUM function adds up all 1s, it will give you a count of unique text values in the specified range. The formula will return the total number of unique text values, excluding the numbers, blank cells, errors, and logical values of TRUE and FALSE.

Counting Unique Numeric Values in Excel

You can use the array formula to count unique numbers in a list of data, just like how you have calculated unique text values. But, the only difference here would be to add ISNUMBER instead of ISTEXT in the formula.

Counting Case-Sensitive Unique Values in Excel

If you want to count case-sensitive data in the Excel list, the easiest way would be to create a helper column with the formula below to identify duplicate and unique items.

  =IF(SUM((--EXACT(range,range)))=1,”UNIQUE”,”DUPE”)

Then you can use a simple COUNTIF function to count the unique value, i.e.,  

  =(COUNTIF(range:”UNIQUE”))

Count Unique Rows in Excel

Counting unique rows in Excel is similar to counting unique values. However, the only difference is that it uses the COUNTIFS function instead of COUNTIF. This lets you specify several columns to check for unique values. So the formula would be:

  =SUM(IF(COUNTIFS(range)=1,1,0))

How to Count Distinct Values in Excel?

For counting the distinct values in Excel, you can use the formula:  

  =SUM(1/COUNTIF(range,range))

Since it is an array formula, you should press the Ctrl+Shift+Enter shortcut. You can also use the SUMPRODUCT function and complete the formula by pressing the Enter key:

  =SUMPRODUCT(1/COUNTIF(range,range))

How Does the Formula Work?

The COUNTIF function is used to determine how often each value appears in the specified range. After this, several division operations are performed, where each array value is used as a divisor with one as the dividend.

Counting Distinct Values Ignoring Empty Cells

If the column in which you want to count distinct values contains blank cells, you must add an IF function. This will check the specified range for blanks. So the formula will be:

 =SUM(IF(range<>””,1/COUNTIF(range,range),0))

Counting Distinct Text Values

For counting the distinct text values in a column, you can use the same approach that we have just used to exclude empty cells. You can use the array formula by adding the ISTEXT function like this:

 =SUM(IF(ISTEXT(range),1/COUNTIF(range,range),””))

Counting Distinct Numbers

For counting the distinct numeric values, you need to use the ISNUMBER function in the assay formula, which will change to:

  =SUM(IF(ISNUMBER(range),1/COUNTIF(range,range),””))

How Do You Count Case-Sensitive Distinct Values in Excel?

The simplest way to count case-sensitive distinct values is by adding a helper column with the array formula that identifies unique values, including the first duplicate occurrences. The basic formula remains the same as unique values with just one change in the cell reference. But this small change makes a huge difference. The formula will be:

 =IF(SUM((--EXACT(range)))=1,”Distinct”,””)

Once the above formula is entered, you will be able to count the distinct values with a usual COUNTIF formula:   =COUNTIF(range,” distinct”)

To  Sum Up

The above formulas and methods make it extremely easy to count unique and distinct values in your Excel list. Make sure that you follow each of the steps carefully to get the desired result.