Nested Subtotals with Excel VBA

Nested subtotals involve creating several levels of nesting for a single data list.

Nested subtotals are calculated as follows:

• Prepare the data list and place the cell pointer within it. Decide which totals you need — by levels of nesting.
• Sort by the required fields (use the Sort dialog box, which opens when executing the Sort command located in the Sort & Filter group on the Data tab of the ribbon).
• Calculate the totals using the Subtotal dialog box (which opens when executing the Subtotal command located in the Outline group on the Data tab of the ribbon).
  When creating nested subtotals, you should clearly understand the levels of totals and create them in the order of increasing detail: first — by the primary sort field, then, disabling the Replace current subtotals option (in the Subtotal dialog box) — by the second field, and so on.

Now let’s look at an example of nested subtotals, using the same sales list. Suppose we need to obtain the total number of products sold by a specific seller, taking into account the particular sale date.

• Select the list (or place the cell pointer in the list) and sort it (execute the Sort command located in the Sort & Filter group on the Data tab of the ribbon) by the fields Seller and Sale Date. To add each subsequent field in the Sort dialog box, click the Add Level button.

• Use the Subtotal command located in the Outline group on the Data tab of the ribbon.
• In the Subtotal dialog box that opens, set the parameters according to obtain the upper (first) level of totals — the total number of products sold by a specific seller.
• To obtain the second level of totals in the same list with the calculated subtotals, place the cell pointer back into the list and then use the Subtotal command again.
• In the Subtotal dialog box that opens, set the parameters according.

• The resulting subtotals are shown in Fig.

NOTE
When subtotals are added to a list, the list layout changes so that its structure becomes visible. By pressing the outline buttons , and , you can create a summary report by hiding details and showing only totals.

The Subtotal Method

The Subtotal method of the Range object adds subtotals to a data list based on changes in specified data fields. Subtotals allow summarizing data. The Subtotal method automatically inserts rows containing formulas that calculate the subtotals. It is necessary to sort the data correctly before activating this method; otherwise, the method may produce incorrect results. The Subtotal method is equivalent to executing the Subtotal command located in the Outline group on the Data tab of the ribbon.

expression.Subtotal(GroupBy, Function, TotalList, Replace, PageBreaks, SummaryBelowData)

• expression — a reference to the cell or the entire range for which the subtotals are calculated.
• GroupBy — required parameter, specifies the field number by which the subtotals are calculated.
• Function — required parameter, specifies the function used for calculating the subtotals. The acceptable values are the following XlConsolidationFunction constants:
  • xlAverage (arithmetic mean),
  • xlCount (number of values),
  • xlCountNums (number of numbers),
  • xlMax (maximum),
  • xlMin (minimum),
  • xlProduct (product),
  • xlStDev (unbiased deviation),
  • xlStDevP (biased deviation),
  • xlSum (sum),
  • xlVar (unbiased variance),
  • xlVarP (biased variance).
• TotalList — required parameter, specifies an array of integers with the numbers of the fields for which subtotals are calculated.
• Replace — optional Boolean parameter. If True, existing subtotals will be replaced.
• PageBreaks — optional Boolean parameter. If True, a page break will be inserted after each group.
• SummaryBelowData — optional parameter that sets the location of the subtotals. Acceptable values are the following XlSummaryRow constants:
  • xlSummaryAbove (subtotals will be displayed above the data),
  • xlSummaryBelow (subtotals will be displayed below the data).

Removing Subtotals

The RemoveSubtotal method of the Range object removes subtotals from a worksheet.
For example, the following instruction removes subtotals associated with the range A1:I40:

Range("A1:I40").RemoveSubtotal