excel 2016 select visible cells only qat

This is a newer more powerful version of the SUBTOTAL function that can perform more calculations and includes more calculation options.Ģ is the number that says which calculation to make.ģ is the option to choose from, which includes ignoring errors and other SUBTOTAL and AGGREGATE functions.ī5:B9 is the range on which to perform the calculation. Aggregate Function - New Way to Count =AGGREGATE(2,3,B5:B9) If using the COUTNA feature, change the 3 to 103.

Changing this to 102 makes the function ignore filtered data and also manually hidden data. =SUBTOTAL(102,B4:B8)ġ02 was 2 in the first example. To make sure the function does not count rows that were manually hidden, we must change the first argument of the function. The above example does not work when rows are manually hidden - right-click a row and click Hide to manually hide a row. Look to the next example to see how to avoid counting rows that were manually hidden. This updates each time a filter is added or removed and also works when no filter at all is used: This counts the cells that are still visible, after a filter has been applied, in the range B4:B8.

Use a 3 instead of a 2 to perform a COUNTA instead of just a regular COUNT. Range_to_count is the range that you want to count. Sections:Īdditional Resources Syntax =SUBTOTAL(2, range_to_count) How to use the COUNT or COUNTA function on a filtered list of data so that hidden rows are not included in the count.

Excel 2016 select visible cells only qat