• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

6. Hacking Formulas and Functions > 71. Make the Excel Subtotal Function Dynami...

Make the Excel Subtotal Function Dynamic

Although SUBTOTAL is one of Excel’s most convenient functions, you sometimes want to choose the function it uses, or apply it to data that can expand and contract.

You use the SUBTOTAL function in Excel to perform a specified function on a range of cells that have had AutoFilters applied to them. When the AutoFilter has been applied, the SUBTOTAL function will use only the visible cells; all hidden rows are ignored. The operation it performs depends solely on the number (between 1 and 11) that you supply to its first argument, Function_num. For example:

=SUBTOTAL(1,A1:A100)

will average all visible cells in the range A1:A100 after AutoFilters have been applied. If all rows in A1:A100 are visible, it will simply average them all and give the same result as:


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint