If you have a larger dataset and you need to apply filters in columns. More importantly, you might require to count only unique values except for the duplicate values. If you’re searching for something like that, then you’re here in the right place. In this instructive session, I’ll demonstrate 5 handy methods to count unique values in a filtered column in Excel with proper explanations.

**Table of Contents**hide

## Download Practice Workbook

## 5 Methods to Count Unique Values in Filtered Column in Excel

Let’s introduce today’s dataset where **Quantity **and **Sales **of some **Product Categories** are provided along with **Order Date** and corresponding **States**. Now, you need to apply filters in columns as well as count the unique values from the filtered column.

You may easily add filters in Excel by using the **AutoFilter** tool in Excel. For your convenience, I am showing the process.

Firstly, select the entire dataset where you want to apply filters, and add the **Filter **tool from the **Sort & Filter** ribbon in the **Data **tab.

Now, you’ll see the drop-down arrow over each header of the dataset. Thus, you can easily filter the data by clicking on the drop-down arrow.

Let’s explore the methods to count unique values in the filtered column.

### 1. Applying Array Formula to Count Unique Values in Filtered Column

In the beginning method, I’ll show you a little bit complex but handy array formula to count unique values after applying the filters in columns.

Assuming that you want to apply the entire dataset based on the **Texas **states. That means you want to keep the data that belongs to the concerning states.

For doing this, just click on the drop-down arrow of the *States *and check the box only before the **Texas **states.

After pressing **OK**, you’ll get the following filtered data.

Now, you may count the unique values of the *Product Category*. So, insert the following array formula in the **B18 **cell.

`=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C5,ROW(C5:C15)-ROW(C5),,1)), IF(C5:C15>"",MATCH("~"&C5:C15,C5:C15&"",0))),ROW(C5:C15)-ROW(C5)+1),1))`

Here, **C5 **is the starting cell of the *Product Category* and **C5:C15** is the cell range of the same field.

**Formula Breakdown:**

In the above formula, the **MATCH** function finds the values in the **C5:C15** cell range and returns {1,2,3,4,5,6,7,8,9,10,11} in an array. Besides, the **ROW** function returns the row number.

Again, the **OFFSET** function returns the reference specified in the **ROW **function. And the **SUBTOTAL** function counts the visible rows. Moreover, the **FREQUENCY** function counts the unique values. Though it ignores the text and zero values, you need to utilize the **IF **function. Lastly, the **SUM** function aggregates all values and returns the number of unique values in the specified cell range.

When you press **ENTER**, you’ll get the following output.

Here, the number of unique values is 4 because the two product categories i.e. **Vegetable **and **Fruit **have duplicates.

**Note:** If you are not a Microsoft 365 user, you have to press **CTRL** + **SHIFT** + **ENTER** while dealing with an array formula.

**Read More: Excel Formula Count Unique Values (3 Easy Ways)**

### 2. Using the COUNTIF Function

Secondly, you may use the **COUNTIF** function to count unique values in the filtered column.

Before finding that, let’s say, you want to filter the dataset based on the **20-Apr-2022** date. So, apply it as shown in the first method.

After filtering the entire dataset, you may apply the **COUNTIF **function using the following formula.

`=(COUNTIF($C$5:$C6,$C6)<2)*1`

Here, **C5 **is the starting cell of the* Product Category* and **C6 **is the starting cell of the filtered field.

In the above formula, the **COUNTIF **function returns 1 for the unique values and 0 for the duplicate values.

If you look closely at the following screenshot, you’ll get an error in the **E12 **cell where **Vegetable **shows 0 but it is a unique category.

So, you need to add a helper column to check the visible rows using the **SUBTOTAL **function.

`=1*SUBTOTAL(3,$D6)`

Here, **D6** is the output found using the **COUNTIF **function and **1** is multiplied to skip the problem of missing the last row in the filter range.

Next, the **IF** logical function is used to show the visible rows.

`=IF($E6,$C6,"-")`

Here, **E6 **is the starting cell of the output found in the previous step, and the **IF **function returns the visible rows. Else it will hyphen(-) for hidden rows.

Again, you have to use the following formula to get the accurate unique values excluding any hidden rows.

`=($F6<>"-")*(COUNTIF($F$6:$F6,$F6)<2)`

Here, **F6 **is the starting cell of the output found in the earlier step.

Lastly, if you apply the **COUNTIF **function to count the unique values (avoiding the zero value), you have to insert the following formula in the **B18 **cell.

`=COUNT(IF(G6:G15<>0, G6:G15))`

Here, **G5:G15** is the cell range of the *Accurate Unique Values.*

**Read More: Count Unique Values with Criteria by COUNTIFS in EXCEL (4 Examples)**

### 3. Combined Use of COUNTA, UNIQUE and FILTER Functions

Luckily, there is another alternative way to execute the same task using the combined use of **COUNTA**, **UNIQUE**, and **FILTER** functions. Before applying the combined formula, you need to count the visible rows using the **SUBTOTAL **function.

`=SUBTOTAL(3,C6)`

Here, **3 **refers to **COUNTA **that counts cells including hidden rows, and **C6 **is the starting cell of the filtered column.

Finally, you may insert the following combined formula.

`=COUNTA(UNIQUE(FILTER(C6:C15,G6:G15)))`

Here, **C6:C15 **and **G6:G15 **are the cell range of the *Product Category* and *Visible Rows* respectively.

In the above formula, the **FILTER **function filters the **C6:C15** cell range based on the value of the **G6:G15** cell range. Later, the **UNIQUE **function finds the unique values and lastly, the **COUNTA **function counts all the filtered and unique values.

**Related Content: How to Use COUNTIF for Unique Text (8 Easiest Ways)**

**Similar Readings:**

**Count Unique Values with Criteria by SUMPRODUCT in Excel****Excel VBA: Count Unique Values in a Column (3 Methods)****How to Count Unique Values in Multiple Columns in Excel (5 Ways)**

### 4. Count Unique Values Using the Advanced Filter

Furthermore, if you wish to count unique values using the **Advanced Filter** tool, you can easily accomplish the task.

Suppose, you want to filter the data having a quantity greater than or equal to 30 (**F4:F6** cell range).

If you want to get the filtered unique data in a new working sheet, then create a new sheet, and choose the **Advanced **tool from the **Sort & Filter** ribbon in the **Data **tab.

While keeping the cursor over the new working sheet (the name of the new working sheet is **Advanced Filter2**), specify the List range as **‘Advanced Filter1’!$B$4:$D$15 **and the Criteria range as **‘Advanced Filter1’!$F$4:$F$5**.

Before doing that, you have to check the circle ahead of the **Copy to another location** option.

*Note: **here, Advanced Filter1 is the name of the existing working sheet whereas the name of the new working sheet is Advanced Fitler2.*

Next, specify the **‘Advanced FIlter2’!$B$4 **after the **Copy to** option, and check the box before the **Unique records only**.

After pressing **OK**, you’ll get the following filtered and unique dataset.

Now, you can simply count the unique values using the **ROWS** function. The formula will be-

`=ROWS(B5:B10)`

Here, **B5:B10 **is the cell range for the filtered *Product Category*.

**Read More:** **How to Count Unique Values Based on Criteria in Another Column in Excel**

### 5. Using the Pivot Table to Count Unique Values in Filtered Column

Apart from these four methods, you might use the **Pivot Table**, one of the powerful features in Excel to analyze the larger dataset efficiently.

However, Creating a Pivot Table is a simple task. Just select the entire dataset and choose the **From Table/Range** from the **Pivot Table** option in the **Insert **tab.

Then, you’ll see the following dialog box. So, you check the circle before the **New Worksheet** and box before the **Add this data to the Data Model**.

And move the *Product Category* into the **Rows **area and *Sales *into the **Values **area.

Now, you right-click while keeping the cursor over a cell of Column **B**. Therefore choose the **Value Field Settings** option.

Finally, choose the** Distinct Count** option as shown in the below picture.

Immediately, you’ll get the following output where the number of unique values is 6.

**Related Content:** **How to Count Unique Values in Excel Using Pivot Table**

## Conclusion

That’s the end of today’s session. I strongly believe you may easily count the unique values in the filtered column in Excel utilizing the above-discussed 5 methods. Anyway, if you have any queries or recommendations, please share them in the comments section.

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C5,ROW(C5:C15)-ROW(C5),,1)), IF(C5:C15>””,MATCH(“~”&C5:C15,C5:C15&””,0))),ROW(C5:C15)-ROW(C5)+1),1))

Above function working file for text. I tried this on Dates but the function is not working

In this situation, you can use an easy alternative. Try using:

=COUNTA(UNIQUE(date range))The above formula can easily count the unique date values.

Hi I found this site on a search. My spreadsheet uses the following formula:

COUNTA(UNIQUE(FILTER(INDIRECT(Dynamic Range),(Criteria1=Value1)*(Criteria2=Value2),””)))

It is essentially the same formula you use aside fom using indirect ranges and an “if empty” condition for the FILTER function. The formula works well as long as the criteria have at least 1 match. If there are no matches, FILTER returns a blank and is counted by COUNTA so the result is 1 instead of 0. Removing the “is empty” condition doesn’t change the result. The problem is that excel will not let me change the COUNTA function to COUNTIFS in the formula which would allow me to exclude the blank entry.

Have you been able to avoid this problem?

Thanks for this! It saved me a lot of work…

Dear

Joris,You are most welcome. We are glad to hear that.

Regards

ExcelDemy