How To Count Filtered Rows In Excel

Premium Excel Course Now Available!

Build Professional - Unbreakable - Forms in Excel

45 Tutorials - 5+ Hours - Downloadable Excel Files

Instant Admission! - Lifetime Access!

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

To do this, use the SUBTOTAL() role.

Sections:

Syntax

Count - Filtered Information

Count - Exclude Manually Hidden Rows

Aggregate Function - New Way to Count

Notes

Additional Resource

Syntax

          =SUBTOTAL(two, range_to_count)

ii tells the role 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 Information

          =SUBTOTAL(2,B4:B8)

This counts the cells that are even so visible, after a filter has been practical, in the range B4:B8.

Event:

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

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

Count - Exclude Manually Subconscious Rows

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

To make certain the function does non count rows that were manually subconscious, we must alter the first argument of the function.

          =SUBTOTAL(102,B4:B8)

102 was 2 in the first example. Changing this to 102 makes the part ignore filtered data and as well manually subconscious data.

Zilch else was changed.

COUNTA

If using the COUTNA feature, alter the three to 103.

Amass Role - New Way to Count

          =Amass(2,iii,B5:B9)

This is a newer more than powerful version of the SUBTOTAL function that can perform more calculations and includes more than 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 office, 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 incorporate numbers.

The COUNTA functionality counts all cells that are not empty.

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

The SUBTOTAL function allows you lot 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 certain to download the file for this tutorial and then you can work with the higher up examples in Excel.

Boosted Resource

Exclude Filtered or Hidden Cells from Calculations

SUBTOTAL Part (microsoft)

Aggregate Function (microsoft)

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!