If the workbook is set to automatically calculate formulas, the Subtotal command recalculates subtotal and grand total values automatically as you edit the detail data. For example, if you use the Average summary function, the grand total row displays an average of all of the detail rows in the list, not an average of the values in the subtotal rows. Grand totals are derived from detail data, not from the values in the subtotals. You can display more than one type of summary function for each column. Subtotals are calculated with a summary function, such as Sum or Average, by using the SUBTOTAL function. Note that this will remove all table functionality from the data except table formatting. To add subtotals in a table, you must first convert the table to a normal range of data, and then add the subtotal. The Subtotal command will appear grayed out if you are working with an Excel table. In horizontal ranges, values in hidden columns are always included.Important: Subtotals are not supported in Excel tables.
![multiple subtotals in excel 2010 multiple subtotals in excel 2010](https://cdn.extendoffice.com/images/stories/shot-kutools-excel/insert-subtotals/shot-paging-subtotal-01.png)
In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.
![multiple subtotals in excel 2010 multiple subtotals in excel 2010](https://www.pivot-table.com/wp-content/uploads/2009/10/multisub01.gif)
![multiple subtotals in excel 2010 multiple subtotals in excel 2010](https://cdn.extendoffice.com/images/stories/doc-excel/table-add-subtotals-total-row/doc-table-add-total-row-1.png)
When function_num is between 101-111, SUBTOTAL excludes values that are hidden.When function_num is between 1-11, SUBTOTAL includes values that are hidden.Note: although the Outline feature is an "easy" way to insert subtotals in a set of data, a Pivot Table is a better and more flexible way to analyze data. In addition, a Pivot Table will separate the data from the presentation of the data, which is a best practice. This allows the subtotal results to remain visible even as rows are hidden and displayed when the outline is collapsed and expanded. SUBTOTAL formulas inserted this way use the standard function numbers 1-11. 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.
![multiple subtotals in excel 2010 multiple subtotals in excel 2010](https://www.excelcampus.com/wp-content/uploads/2020/06/Subtotals-are-adding-rows-with-the-same-product.png)
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