The Subtotal dialog allows end-users to automatically group related data in a worksheet and add summary rows to each group using the SUBTOTAL function.
Add the Outline ribbon group to enable the Subtotal button, which invokes the target dialog. Refer to the Lesson 1 - Create a Simple Spreadsheet topic for details on how to provide a Ribbon UI for the SpreadsheetControl.
Before subtotaling, make sure that the target range meets the following requirements:
It contains similar data in each column;
It has column headings in the first row;
It does not include blank rows or columns.
The Subtotal dialog allows end-users to set the following subtotal options:
At each change in
Specifies a column heading by which the data should be grouped. Each time a value in this column changes, a new subtotal row is inserted. Sort your data by the target column to ensure that the same column values will be in one group.
Sets the function to calculate subtotals. Eleven functions are available: Sum, Count, Average, Max, Min, Product, Product Numbers, StdDev, StdDevp, Var or Varp.
Add subtotals to
Specifies column(s) for which the SUBTOTAL function should be calculated.
Replace current subtotals
Sets whether existing subtotals should be replaced with the recently specified ones. Clear this check box if you wish to create more than one level of subtotals (for example, to insert subtotals for the inner groups using different summary functions).
Page break between groups
Locates each group on a new page. It can be useful if your groups contain many detail rows and you wish to separate them while printing.
Summary below data
Sets whether a summary row should be displayed below detail rows in each group. Without this option specified, the summary row will be displayed at the top of the group.
As a result, the SUBTOTAL function appears as in the image below:
The number (1-11 or 101-111) specifies a function to use for the subtotal. 1-11 includes hidden rows, while 101-111 excludes them. End-users can change this argument in the cell or formula bar. The table below lists functions and corresponding numbers.