Excel Formula Count Unique Values (3 Easy Ways)

Working with a large dataset in Excel, you may often need to know how many duplicate and unique values are there. You are definitely not going to want to count this manually and moreover, it can be time-consuming in most cases. This tutorial will show you how you can count unique values using Excel formula.

Before beginning, let’s get to know about today’s example workbook.

Workbook - Excel Formula Count Unique Values

Here we have a concise data set of a few employees with their Address and Salary. For keeping things simpler we are using this basic set of data.

In the practical world, you may encounter with much complex data sets and relationships.

Using the Employee First Name we will show how to count unique text values and using the Salary column we will show how to do it for numbers.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Excel Advanced Sort & Filter option

You can use the Sort & Filter option for counting unique or distinct values.

Select the cells or column you want to extract unique values.

Then on the Data tab you will find the Sort & Filter option. Inside there, you will notice the Advanced option.

Advanced sort-Excel Formula Count Unique Values

Click Advanced. A new dialog box will pop up in front of you.

Dialog box

Now select Copy to another location and insert where you want to copy the data.

Do not forget to check Unique records only. 

Input in Dialog box - Excel Formula Count Unique Values

You will get the unique values from your selected cells.

Unique values

Now you can further count them using the ROWS function.

ROWS-Excel Formula Count Unique Values

It gives you the total of unique values.

You can use the COUNTA function as well, since it will count irrespective of the type of your values.

COUNTA-Excel Formula Count Unique Values

Functions You May Need to Know

1. COUNTIF

The COUNTIF function counts cells in a range that meets a particular condition.

Syntax of the COUNTIF function is as follows

COUNTIF (range, criteria)

range: The range of cells you want to count

criteria: The criteria that evaluate which cells should be counted

To know more about the COUNTIF function you can explore Microsoft Support site.

2. FREQUENCY

The FREQUENCY function calculates how often values occur within a range of values. It returns a vertical array of numbers.

Syntax of FREQUENCY function is

FREQUENCY (data_array, bins_array)

data_array: An array of or reference for which you want to count frequencies.

bins_array: An array of or reference to intervals into which you want to group the values in data_array.

You can know more about FREQUENCY function from Microsoft Support site.

3. SUMPRODUCT

The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays.

SUMPRODUCT(N number of array)

N can be any number equal or more than 1. 

To know more about the SUMPRODUCT function you can explore Microsoft Support site.

4. MATCH

The MATCH function is used to locate the position of a specified item in a range of cells. It returns the relative position of a particular item in the range.

Syntax of the MATCH function is

MATCH(lookup_value, lookup_array, match_type)

lookup_value: The value to search in the lookup_array.

lookup_array: A range of cells that are being searched.

match_type: This is an optional field. You can insert 3 values.

1 = Smaller or equal to lookup_value

0 = Exact lookup_value

-1 = Greater or equal to lookup_value

For a deeper understanding, you can check out the Microsoft support site.

Formula Count Unique Values

1. Count Unique Text values

1.1. Using Basic SUMPRODUCT-COUNTIF

We will use SUMPRODUCT and COUNTIF function to extract the number of unique presents within a range.

Let’s see the generic formula first.

SUMPRODUCT(1/COUNTIF(data_range, criteria_range))

SUMPRODUCT

Here our data_range and criteria_range will be the same. Because for each value in the data range, COUNTIF will check how many times that occurs in the criteria_range, so our data_range and criteria_range should be the same.

Inside mechanism of the COUNTIF function will result in an array that contains the occurrence number for each value.

For understanding, just insert the COUNTIF portion of this formula in Excel.

COUNTIF-Excel Formula Count Unique Values

And instead of Enter, hit F9 key.

Array COUNTIF

You will see an array of {3;3;3;2;2;1;2;2;1;2;2;1}. Here 3 represents – John, who appears 3 times; first 2 for Johnny, who appears 2 times, and so on.

Now dividing 1 by this array we will find another array

{0.3333;0.3333;0.3333;0.5;0.5;1;0.5;0.5;1;0.5;0.5;1}

Then SUMPRODUCT will sum all the values inside the array and return the result.

Now write the formula in Excel.

SUMPRODUCT-COUNTIF-Excel Formula Count Unique Values

It gave the total. There is a total of 7 unique names here.

This formula has a fault. If any of your rows is empty then it will give an error.

Error-Excel Formula Count Unique Values

All we need to use a IF statement where.

SUMPRODUCT(IF(data_range<> “”,1/COUNTIF(data_range,criteria_range),0))

Inside the IF statement we are checking whether any empty cell or not and then exercise the COUNTIF function.

Write the formula in Excel.

Error Free-Excel Formula Count Unique Values

It worked fine.

➥ Related: Count Unique Values with Criteria by SUMPRODUCT in Excel

1.2. Using Array to count

So far we have discussed the formula which is useful for small ranges. The SUMPRODUCT-COUNTIF formula will get slower with the increase of range.

To resolve this issue, we can use the MATCH function with FREQUENCY. Our generic formula will be

SUMPRODUCT(IF(FREQUENCY(MATCH(data_range,data_range,0),MATCH(data_range,data_range,0))>0,1))

MATCH-Excel Formula Count Unique Values

Here FREQUENCY function gives an array containing the number of occurrences for a value for the first occurrence and for the next occurrence of that value, it returns 0.

The MATCH function is used for returning the position in the range.

These two MATCH functions returned the position and then used as an argument to the FREQUENCY function which gives us a count of the total number of distinct values.

Write the formula in Excel.

Result Match-Excel Formula Count Unique Values

For keeping things simple and similar we are using the same set of examples, you will find this formula faster compared to the earlier one while dealing with a large data set.

Let’s see another array formula method.

SUMPRODUCT(IF(FREQUENCY(IF(data_range<>"", MATCH(data_range,data_range,0)),ROW(data_range)-ROW(first_cell)+1),1))

ROW-Excel Formula Count Unique Values

Again this formula uses the FREQUENCY function to count the unique values. And we will use the MATCH and ROW function within the FREQUENCY function to provide its two placeholders.

The ROW function returns the row number of each cell in range. Since we subtracted the first – row number from each row, it returns an array of serial from the value 1.

The MATCH function in this formula will give us the position number of each item that appears in the data range. It will return values as an array.

Inside the IF statement to check for any empty cell.

These arrays are fed to the FREQUENCY function which returns how often values occur within the set of data.

The outer IF function sets each unique value to 1 and duplicate value to FALSE.

And the SUMPRODUCT counts the non-zero elements within the array and gives the total.

Result-Excel Formula Count Unique Values

Read more: How to Use COUNTIF for Unique Text

2. Count Unique Numbers

2.1. Using FREQUENCY Function

Counting unique numbers will be much easier using the FREQUENCY function. This function is typically for the numbers that’s why we couldn’t use it directly for the text values.

Let’s write the generic formula first

SUMPRODUCT(IF(FREQUENCY(data_range,data_range),1))

Count numbers-Excel Formula Count Unique Values

Similarly, to the COUNTIF function, FREQUENCY function returns an array of each value occurrence. But only for numbers.

Just for check, write the FREQUENCY function and provide the data_range within it.

FREQUENCY-Excel Formula Count Unique Values

Hit F9 key. You will see an array.

Frequency array-Excel Formula Count Unique Values

FREQUENCY function returns the occurrence number for the first one and 0 for the rest of the same value.

IF function will interpret the non-zero elements as TRUE and 0 as FALSE. For each non-zero value it will set 1 in the array.

Then with the SUMPRODUCT function it will provide the total amount.

Write it in Excel.

SUMPRODUCT-FREQUENCY-Excel Formula Count Unique Values

Instead of the IF function we can write the formula using the SIGN function.

Earlier, we have set the IF function in such a way that it will replace non-zero as 1. But SIGN will do it automatically.

SUMPRODUCT(SIGN(FREQUENCY(data_range,data_range))

SIGN

SIGN interprets all the non-zero (positive) numbers as 1 and zero as 0.  

SIGN output

Write the SIGN – FREQUENCY portion of this formula first and press F9.

SIGN array

If you can remember the array returned by the FREQUENCY function ( can scroll up and see ) then you will understand that for non-zero elements SIGN provided 1.

Then use SUMPRODUCT to get the total output.

SIGN- result

2.2. Using ISNUMBER Function

For numbers you can use the ISNUMBER function. Our generic formula using ISNUMBER function will be something like:

SUMPRODUCT(1/COUNTIF(data_range, data_range),--ISNUMBER(data_range))

ISNUMBER formula

The COUNTIF function here, will result in an array that contains the instance number for each value.

The ISNUMBER function here, will result in an array of values TRUE for numbers and False for others. Adding two hyphens (-) before ISNUMBER will convert TRUE into 1.

ISNUMBER result-Excel Formula Count Unique Values

3. UNIQUE Function to Count Values

If you are using EXCEL 365, then in order to count unique values, you can use the UNIQUE function as well.

To count the unique values using the UNIQUE function, our formula will be

COUNTA(UNIQUE(data_range))

UNIQUE-Excel Formula Count Unique Values

The UNIQUE function will fetch the non-duplicate values and the COUNTA function will count the non empty cells within range.

So, using the UNIQUE function inside a COUNTA function lead us to the result we are looking for.

UNIQUE result-Excel Formula Count Unique Values

This same formula will work for numbers as well.

Number unique

Conclusion

That’s all for today. We have tried listing out several formulas to count unique values. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. You are welcome to notify us of any other methods to the task.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo