Count the Visible Rows in a Filtered List in Excel

Add to Favorites
Author: | Edits: don

How to use the COUNT or COUNTA function on a filtered list of data so that hidden rows are not included in the count.

To do this, use the SUBTOTAL() function.

Sections:

Syntax

Count - Filtered Data

Count - Exclude Manually Hidden Rows

Aggregate Function - New Way to Count

Notes

Additional Resources

Syntax

=SUBTOTAL(2, range_to_count)

2 tells the function to perform a count.

range_to_count is the range that you want to count.

COUNTA

Use a 3 instead of a 2 to perform a COUNTA instead of just a regular COUNT.

Count - Filtered Data

=SUBTOTAL(2,B4:B8)

This counts the cells that are still visible, after a filter has been applied, in the range B4:B8.

4a7fd587b143d8be216a086ab15a3fd6.png

Result:

de73326fa5a0c1d5cde3570683e0a6c3.png

This updates each time a filter is added or removed and also works when no filter at all is used:

97507f607b908354b7735b9709979fe6.png

07c2d53a86b9e2f05b82a267fa2bb780.png

Look to the next example to see how to avoid counting rows that were manually hidden.

Count - Exclude Manually Hidden Rows

The above example does not work when rows are manually hidden - right-click a row and click Hide to manually hide a row.

To make sure the function does not count rows that were manually hidden, we must change the first argument of the function.

=SUBTOTAL(102,B4:B8)

102 was 2 in the first example. Changing this to 102 makes the function ignore filtered data and also manually hidden data.

Nothing else was changed.

COUNTA

If using the COUTNA feature, change the 3 to 103.

Aggregate Function - New Way to Count

=AGGREGATE(2,3,B5:B9)

This is a newer more powerful version of the SUBTOTAL function that can perform more calculations and includes more calculation options.

2 is the number that says which calculation to make.

3 is the option to choose from, which includes ignoring errors and other SUBTOTAL and AGGREGATE functions.

B5:B9 is the range on which to perform the calculation.

When you input this function, you will see a list of options for the first two arguments, and they are very clear and easy to understand.

Notes

The COUNT functionality only counts cells that contain numbers.

The COUNTA functionality counts all cells that are not empty.

As such, it may be better for you to use the COUNTA functionality (either 3 or 103) for the SUBTOTAL function.

The SUBTOTAL function allows you to apply a number of regular functions to filtered data, for a full list and explanation, view our tutorial on the SUBTOTAL function in Excel.

Make sure to download the file for this tutorial so you can work with the above examples in Excel.

Additional Resources

Exclude Filtered or Hidden Cells from Calculations

SUBTOTAL Function (microsoft)

AGGREGATE Function (microsoft)

 


Excel Function: AGGREGATE(), SUBTOTAL()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

Similar Content on TeachExcel
Average the Visible Rows in a Filtered List in Excel
Tutorial: Average the results from a filtered list in Excel. This method averages only the visible ...
Sum the Visible Rows from a Filtered List in Excel
Tutorial: How to SUM only the visible rows from a filtered data set in Excel. To do this, we will us...
PMT Function - Get the Payment Due for a Loan in Excel
Tutorial: How to calculate the payment amount for a loan or similar financial instrument that has a ...
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...
Filter Data in Excel - AutoFilter
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Formula to Get the Last Value from a List in Excel
Tutorial: Formulas that you can use to get the value of the last non-empty cell in a range in Excel...
Tutorial Details
Excel Function: AGGREGATE(), SUBTOTAL()
Downloadable Files: Excel File
Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)