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.

Dataset Excel Count Unique Values in 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.

How to Add Filter

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.

Excel Count Unique Values in Filtered Column Applying Array Formula

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

Excel Count Unique Values in Filtered Column Applying Array Formula

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.

Excel Count Unique Values in Filtered Column Applying Array Formula

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.

Excel Count Unique Values in Filtered Column Applying Array Formula

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.

Using the COUNTIF Function

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.

Using the COUNTIF Function

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.

Using the COUNTIF Function

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.

Using the COUNTIF Function

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.

Using the COUNTIF Function

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.

Using the COUNTIF Function

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.

Excel Count Unique Values in Filtered Column Combined Use of COUNTA, UNIQUE and FILTER Functions

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.

Excel Count Unique Values in Filtered Column Combined Use of COUNTA, UNIQUE and FILTER Functions

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


Similar Readings:


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

Using the Advanced Filter Tool

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.

Using the Advanced Filter Tool

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.

Using the Advanced Filter Tool

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.

Using the Advanced Filter Tool

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

Using the Advanced Filter Tool

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.

Using the Advanced Filter Tool

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.

Excel Count Unique Values in Filtered Column Using the Pivot Table

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.

Excel Count Unique Values in Filtered Column Using the Pivot Table

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

Excel Count Unique Values in Filtered Column Using the Pivot Table

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

Excel Count Unique Values in Filtered Column Using the Pivot Table

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

Excel Count Unique Values in Filtered Column Using the Pivot Table

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

Excel Count Unique Values in Filtered Column Using the Pivot Table

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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

3 Comments
  1. =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.

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

Leave a reply

ExcelDemy
Logo