NamedRange.Subtotal Method

Creates subtotals for the NamedRange control.

Namespace:  Microsoft.Office.Tools.Excel
Assembly:  Microsoft.Office.Tools.Excel (in Microsoft.Office.Tools.Excel.dll)

Syntax

'Declaration
Function Subtotal ( _
    GroupBy As Integer, _
    Function As XlConsolidationFunction, _
    TotalList As Object, _
    Replace As Object, _
    PageBreaks As Object, _
    SummaryBelowData As XlSummaryRow _
) As Object

Object Subtotal(
    int GroupBy,
    XlConsolidationFunction Function,
    Object TotalList,
    Object Replace,
    Object PageBreaks,
    XlSummaryRow SummaryBelowData
)

Parameters

• GroupBy
  Type: System.Int32

  The field to group by, as a one-based integer offset.

• Function
  Type: Microsoft.Office.Interop.Excel.XlConsolidationFunction

  The subtotal function.

  Can be one of the following XlConsolidationFunction values:

  xlAverage

  xlCount

  xlCountNums

  xlMax

  xlMin

  xlProduct

  xlStDev

  xlStDevP

  xlSum

  xlUnknown

  xlVar

  xlVarP

• TotalList
  Type: System.Object

  An array of 1-based field offsets, indicating the fields to which the subtotals are added.

• Replace
  Type: System.Object

  true to replace existing subtotals. The default value is false.

• PageBreaks
  Type: System.Object

  true to add page breaks after each group. The default value is false.

• SummaryBelowData
  Type: Microsoft.Office.Interop.Excel.XlSummaryRow

  Places the summary data relative to the subtotal.

  Can be one of the following XlSummaryRow values:

  xlSummaryAbove

  xlSummaryBelow (default)

Return Value

Type: System.Object

Remarks

Optional Parameters

For information on optional parameters, see Optional Parameters in Office Solutions.

Examples

The following code example creates subtotals for a NamedRange. The subtotals are the sum of all three fields in the NamedRange.

This example is for a document-level customization.

Private Sub CreateSubtotal()
        Me.Range("A1").Value2 = "Row 1" 
        Me.Range("B1").Value2 = "Row 2" 
        Me.Range("C1").Value2 = "Row 3" 

        Me.Range("A2", "A5").Value2 = 10
        Me.Range("B2", "B5").Value2 = 20
        Me.Range("C2", "C5").Value2 = 30

        Dim namedRange1 As Microsoft.Office.Tools.Excel.NamedRange _
            = Me.Controls.AddNamedRange(Me.Range("A1", "C5"), _
            "namedRange1")

        Dim fields() As Integer = {1, 2, 3}
        namedRange1.Subtotal(1, Excel.XlConsolidationFunction.xlSum, _
            fields, , , Excel.XlSummaryRow.xlSummaryBelow)
    End Sub

private void CreateSubtotal()
{
    this.Range["A1"].Value2 = "Row 1";
    this.Range["B1"].Value2 = "Row 2";
    this.Range["C1"].Value2 = "Row 3";

    this.Range["A2", "A5"].Value2 = 10;
    this.Range["B2", "B5"].Value2 = 20;
    this.Range["C2", "C5"].Value2 = 30;

    Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
        this.Controls.AddNamedRange(this.Range["A1", "C5"],
        "namedRange1");

    int[] fields = new int[] { 1, 2, 3 };
    namedRange1.Subtotal(1, Excel.XlConsolidationFunction.xlSum,
        fields, Excel.XlSummaryRow.xlSummaryBelow);
}

.NET Framework Security

• Full trust for the immediate caller. This member cannot be used by partially trusted code. For more information, see Using Libraries from Partially Trusted Code.

See Also

Reference

NamedRange Interface

Microsoft.Office.Tools.Excel Namespace