Count Unique Values in a Filtered Column in Excel

Powerful spreadsheet software like Excel provides a variety of features to alter and analyse data. Finding the number of distinct values in a filtered column is a typical task that can be helpful when working with huge datasets. Excel data filtering enables you to focus on particular subsets of your data by only displaying rows that match specified criteria. The ordinary COUNT function, however, merely counts the visible cells, including any duplicates, when a column is filtered. We need to use a different strategy in order to precisely count the unique values in a filtered column.

In this article, we'll look at a step-by-step procedure for counting the number of distinct values in an Excel column that has been filtered. You will clearly understand how to extract distinct values from a filtered column and produce an accurate count by the end of this course. In order to efficiently count unique values in a filtered column in Excel, let's get started.

Count Unique Values in a Filtered Column

Here, we will complete the task using the formula directly. So let us see a simple process to learn how you can count unique values in a filtered column in Excel.

Step

Consider an Excel sheet where you have filtered columns similar to the below image.

First, to count the unique values in a filtered column, click on an empty cell in the case of cell D2 and enter the formula as =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A12)-ROW(A2),,1)),IF(A2:A12<>"",MATCH("~"&A2:A12,A2:A12&"",0))),ROW(A2:A12)-ROW(A2)+1),1)). In the formula D2:D22, the range of the filtered cell

Empty Cell > Formula > Enter.

This is how you can count unique values in a filtered column in Excel.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can count unique values in a filtered column in Excel to highlight a particular set of data.