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.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

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

__Arguments:__

Argument | Required/Optional | Explanation |
---|---|---|

range |
Required | Range of cells where the criteria will be assigned for counting. |

criteria |
Required | Condition or criterion for the selected range of cells. |

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 the** FREQUENCY** function is

**FREQUENCY (data_array, bins_array)**

__Arguments:__

Arguments |
Required/Optional |
Explanation |
---|---|---|

data_array |
Required | It is an array of values for which you want to get frequencies. |

bins_array |
Required | It is an array of intervals for grouping values. The interval is also known as “bins”. |

You can know more about the **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.*

__Arguments:__

Argument |
Required or Optional |
Value |
---|---|---|

array 1 |
Required | The first array of numbers. |

[array2] |
Optional | The second array of numbers. |

[array3] |
Optional | The third array of numbers. |

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

__Arguments:__

Argument | Required/Optional | Explanation |
---|---|---|

lookup_value |
Required | The value to match in the array |

lookup_array |
Required | A range of cells or an array reference where to find value |

match_type |
Optional | Specifies how Excel matches lookup_value with values in lookup_array. Here, 1 = exact or next smallest, 0 = exact match and -1 = exact or next largest |

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

**4 Simple Methods to Count Unique Values with Formula in Excel**

In the following, I have described 4 simple and quick methods to count unique values with formula in Excel.

Here we have a concise data set of a few **Employees** with their **Addresses **and** Salaries**. For keeping things simpler we are using this basic set of data.

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

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

### 1. Using Excel Advanced Sort & Filter Option

You can use the **Sort & Filter** option for counting unique values using formula in Excel.

**Steps:**

- First, select the
**cells**or column you want to extract unique values. - Then on the
**Data**tab you will find the**Sort & Filter**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 select range from the table. - Then, clicking the “
**Copy to**” section choose a**cell**(**F5**) to copy unique values. - Do not forget to check
**Unique records only***.* - Finish the procedure clicking
**OK**.

- You will get the unique values from your selected cells.

- Now you can further count them using the
**ROWS****function**to count unique values. Simply, choose a**cell**(**F14**) and apply the below formula down-

`=ROWS(F4:F10)`

- Gently, hit
**ENTER**and you will get the unique values counted in the selected cell.

You can also use the **COUNTA** **function** as well, since it will count irrespective of the type of your values. Similarly, select a **cell** (**F16** ) and write the below formula-

`=COUNTA(F4:F10)`

- Similarly, click
**ENTER**and you will get the output in your hands.

### 2. Counting Unique Text Values Using Multiple Formula

#### 2.1. Using Basic SUMPRODUCT and COUNTIF Functions

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

**Steps:**

- Starting with, choose a
**cell**(**F5**) and apply the below formula-

`=SUMPRODUCT(1/COUNTIF(B5:B16,B5:B16))`

**Formula Breakdown:**

- 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. Then**SUMPRODUCT**will sum all the values inside the array and return the result.

#### 2.2. Combine IF, COUNTIF, and SUMPRODUCT Functions

Sometimes you might find **blank cells **inside a range. In that case, after applying formula, you will get to see “**#Div/0!**” error. Well, with the combination of **IF**, **COUNTIF**, and **SUMPRODUCT functions **you can count unique text values whether there is a blank cell inside the range. Follow the instructions below-

**Steps:**

- Simply, choose a
**cell**(**F5**) and write the formula down-

`SUMPRODUCT(IF(B5:B16<>"",1/COUNTIF(B5:B16, B5:B16), 0))`

- Hence, press
**ENTER**to get the output.

**Read More:** **Count Unique Values with Criteria by SUMPRODUCT in Excel**

#### 2.3. 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** and **FREQUENCY** **functions**.

**Step 1:**

- First, choose a
**cell**(**F5**) and put the formula down-

`=SUMPRODUCT(IF(FREQUENCY(MATCH(B5:B16,B5:B16,0),MATCH(B5:B16,B5:B16,0))>0,1))`

**Formula Breakdown:**

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

**ENTER**key from the keyboard to get the final output.

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. You can also try the combination of the **ROW**, **MATCH**, **FREQUENCY**, **IF**, **SUMPRODUCT functions**.

**Step 2:**

- Just like the previous methods, choose a
**cell**(**F5**) and write the below formula inside the cell-

`=SUMPRODUCT(IF(FREQUENCY(IF(B5:B16<>"",MATCH(B5:B16,B5:B16,0)),ROW(B5:B16)-ROW(B5)+1),1))`

** **

**Formula Breakdown:**

- Again this formula uses the
**FREQUENCY****function**to count the unique values. And we will use the**MATCH**and**ROW**functions 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.

**ENTER**to get the final output.

**Read More:** **How to Use COUNTIF for Unique Text**

### 3. Count Unique Numbers

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

**Step 1:**

- Write the below formula down in the selected
**cell**(**F5**)-

`=SUMPRODUCT(IF(FREQUENCY(D5:D16,D5:D16),1))`

**Formula Breakdown:**

- Similarly, to the
**COUNTIF****function**,**FREQUENCY****function**returns an array of each value occurrence. But only for numbers. - Here, the
**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.

**ENTER**and you will get the unique values counted.

Instead of the **IF function** we can write the formula using the **SIGN** **function**. This is the simplest Excel formula to count unique values.

**Step 2:**

- Presently, choose a
**cell**(**F5**) and apply the below formula-

`=SUMPRODUCT(SIGN(FREQUENCY(D5:D16,D5:D16)))`

**Formula Breakdown:**

- Earlier, we have set the
**IF****function**in such a way that it will replace**non-zero**as**1**. But the**SIGN****function**will do it automatically.**SIGN**interprets all the**non-zero**(**positive**)**numbers**as**1**and**zero**as**0**. - 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**.

**ENTER**to get the counting completed for unique numbers.

#### 3.2. Using ISNUMBER Function

For counting unique numbers you can use the **ISNUMBER** **function** too. Follow the steps below-

**Steps:**

- Simply, choose a
**cell**(**F5**) and apply the formula below-

`=SUMPRODUCT(1/COUNTIF(D5:D16,D5:D16),--ISNUMBER(D5:D16))`

**Formula Breakdown:**

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

**ENTER**button to get the final output.

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

**Steps:**

- First, select a
**cell**(**F5**) and write the below formula inside the cell-

`=COUNTA(UNIQUE(B5:B16))`

**Formula Breakdown:**

- 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**leads us to the result we are looking for.

**ENTER**and you will get your desired result. 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 in Excel. 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.