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.

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.

**Table of Contents**hide

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

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

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

Do not forget to check *Unique records only. *

You will get the unique values from your selected cells.

Now you can further count them using the **ROWS** function.

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.

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

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.

And instead of Enter, hit **F9 **key.

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.

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.

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.

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

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.

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

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.

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

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.

Hit **F9** key. You will see an array.

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

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** interprets all the non-zero (positive) numbers as 1 and zero as 0. *

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

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.

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

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.

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

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.

This same formula will work for numbers as well.

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