How to Count Unique Values in Filtered Column in Excel (5 Methods)

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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-

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.

Related Articles

• Count Unique Text Values with Criteria in Excel (5 Methods)
• COUNTIFS Unique Values in Excel (3 Easy Ways)
• How to Count Unique Names in Excel (5 Methods)