How to count filtered rows in excel
To count filtered rows in Excel, you can use the SUBTOTAL function that allows you to select its count feature in the first argument and the range from which you want to count filtered rows. After using this formula, when you apply a filter on that range and filter cells, it will only count the visible rows.
Steps to Count Filtered Rows
- First, in cell B2, enter the function SUBTOTAL.
- Now, in the first argument, select function_num COUNTA or enter 3.
- After that, in the second argument, refer to the range A1:A101.
- Next, enter the closing parentheses to close the function and enter.
- In the end, filter column A to see that your formula shows you the count of filtered rows in the result.
Using this SUBTOTAL function helps you in other ways, too. Let’s say you have a few rows hidden, in that case also, it will give you a count without those hidden rows.
In the above example, we have four rows hidden, and it has returned 96 in the result.
Remember, there are two different ways to count rows once you apply the filter.
- Count with Hidden Rows
- Count without Hidden Rows
Counting filtered rows in Excel is a useful skill that can save you time and effort when working with large sets of data. Whether you prefer to use the Status Bar, the SUBTOTAL function, or the AGGREGATE function, there are multiple ways to count filtered rows in Excel. By mastering these techniques, you can become a more efficient and effective Excel user.
Top 25 Microsoft Excel Formulas
Introduction : Microsoft Excel is the go-to tool for working with data. There are probably a handful of people who haven’t used Excel, given its im...
Advantages Of Ms Excel And Advantages of Ms Word
Welcome to our blog dedicated to Microsoft Excel and Microsoft Word! In this blog, we explore the numerous advantages of using these powerful software applications in your professional and personal life. Microsoft Excel is a versatile spreadsheet program that enables you to perform complex calculations, create charts and diagrams, and compile and analyze data effortlessly. It is widely used in finance, accounting, education, and various industries. We discuss how Excel simplifies data processing, offers easy data modification and control, facilitates report printing, provides useful templates, allows for automation through coding, and much more. Join us as we explore the advantages of these indispensable Microsoft applications and discover how they can streamline your work processes, improve efficiency, and unlock your full potential in data processing and content creation.