Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to 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.
481 Views
