# Count the Visible Rows in a Filtered List in Excel

Add to Favorites
Author:

How to use the COUNT 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

Example - Filtered Data

Example - Exclude Manually Hidden Rows

Notes

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

## Example - 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. Result: This updates each time a filter is added or removed and also works when no filter at all is used:  Look to the next example to see how to avoid counting rows that were manually hidden.

## Example - 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.

## Question? Ask it in our Excel Forum

Excel Function: SUBTOTAL()
Downloadable Files: Excel File

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...
Delete Duplicate Values in All Versions of Excel
Tutorial: How to delete duplicate values from a data set in all versions of Excel.  This includes E...
Tutorial Details
Excel Function: SUBTOTAL()
Downloadable Files: Excel File
Similar Content
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 ...