Another way to solve this problem is with theAGGREGATE function.
Both formulas are explained below.
on a set of data, returning anaggregatedresult.
Forreference, we use the full range for the amounts in column F, which is F7:F19.
To ignore rows that are hidden manually (i.e.
Using 109 instead of 9 simply changes the behavior to also ignore manually hidden rows.
SUBTOTAL function can perform many other calculations as well.
See a complete liston this page.
AGGREGATE function
TheAGGREGATE functionis like an ungraded version of SUBTOTAL.
Like SUBTOTAL, AGGREGATE can run many aggregate calculations, optionally ignoring hidden rows.
However, AGGREGATE provides more calculation options, and more options for ignoring things.
AGGREGATE provides a total of 19 different calculations and many options for ignoring things.
For details, see ourAGGREGATE function page.
Note: One subtle difference between AGGREGATE and SUBTOTAL is that default behavior for hidden rows is reversed.
