You can find this feature at Data > Outline > Subtotal. SUBTOTAL with outlinesĮxcel has a Subtotal feature that automatically inserts SUBTOTAL formulas in sorted data. That is, as you filter rows in a table with a Total row, calculations automatically respect the filter. Excel uses SUBTOTAL for calculations in the Total row of an Excel Table because SUBTOTAL automatically excludes rows hidden by the filter controls at the top of the table. Excel inserts the SUBTOTAL function automatically, and you can use a drop-down menu to switch behavior and show max, min, average, etc. The SUBTOTAL function is used when you display a Total row in an Excel Table. Values in rows that have been "filtered out" are never included, regardless of function_num. Note: SUBTOTAL always ignores values in cells that are hidden with a filter. When function_num is between 101-111, SUBTOTAL excludes values in rows that have been manually hidden. When function_num is between 1-11, SUBTOTAL includes cells that have been manually hidden.
This is related to how SUBTOTAL deals with manually hidden rows.
There are 11 functions available, each with two options, as seen in the table below. Notice these values are "paired" (e.g. SUBTOTAL behavior is controlled by the function_num argument, which is provided as a numeric value. = SUBTOTAL ( 3 ,B7:B19 ) // count visible = SUBTOTAL ( 9 ,F7:F19 ) // sum visible Available calculations
0 kommentar(er)
