The requested page is not available for the requested platform. You are viewing the content for .NET Framework 4.5.2+ platform.
.NET Framework 4.5.2+
.NET Framework 4.5.2+
.NET Standard 2.0+
Row

Worksheet.Subtotal(Range, Int32, List<Int32>, Int32, String) Method

Creates subtotals for the specified range of cells.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v19.1.Core.dll

Declaration

void Subtotal(
    Range range,
    int groupByColumn,
    List<int> subtotalColumnList,
    int functionCode,
    string functionText
)
Sub Subtotal(
    range As Range,
    groupByColumn As Integer,
    subtotalColumnList As List(Of Integer),
    functionCode As Integer,
    functionText As String
)
void Subtotal(
    Range range,
    int groupByColumn,
    List<int> subtotalColumnList,
    int functionCode,
    string functionText
)
Sub Subtotal(
    range As Range,
    groupByColumn As Integer,
    subtotalColumnList As List(Of Integer),
    functionCode As Integer,
    functionText As String
)

Parameters

Name Type Description
range Range

A Range object that is the range of cells for which the subtotals should be created.

groupByColumn Int32

An integer that specifies the zero-based index of a column containing related data for grouping.

subtotalColumnList List<Int32>

A list of column indexes in a worksheet that defines columns for which the subtotals should be calculated.

functionCode Int32

An integer that specifies the code of the function to be used in calculating subtotals.

functionText String

A string that defines the text to be displayed in the summary rows.

Parameters

Name Type Description
range Range

A Range object that is the range of cells for which the subtotals should be created.

groupByColumn Int32

An integer that specifies the zero-based index of a column containing related data for grouping.

subtotalColumnList List<Int32>

A list of column indexes in a worksheet that defines columns for which the subtotals should be calculated.

functionCode Int32

An integer that specifies the code of the function to be used in calculating subtotals.

functionText String

A string that defines the text to be displayed in the summary rows.

Remarks

Before subtotaling your data, make sure that the range you wish to subtotal has columns headings and the column defined by the groupByColumn parameter contains repetitive values. Each time a value in this column changes, a new subtotal row is inserted, so that it is better to sort your data to ensure that the same values in the groupByColumn column will be in one group.

The table below provides a full list of functions that can be used in calculating subtotals.

functionCode

(includes hidden values)

functionCode

(ignores hidden values)

Function Name

fuctionText

(default)

1

101

AVERAGE

Average

2

102

COUNT

Count

3

103

COUNTA

Count

4

104

MAX

Max

5

105

MIN

Min

6

106

PRODUCT

Product

7

107

STDEV

StdDev

8

108

STDEVP

StdDevp

9

109

SUM

Total

10

110

VAR

Var

11

111

VARP

Varp

Examples

The example below demonstrates how to use the Worksheet.Subtotal method to automatically create outlines for a sorted range and summarize data in each group using the SUBTOTAL function.

Important

Before subtotaling, make sure that the range you wish to subtotal has column headings in the first row and the data in this range is sorted by a column at each change in which a subtotal row will be inserted.

To insert subtotals, do the following.

  1. Specify the Range object, which contains data you wish to subtotal
  2. Create a list of column indexes that define columns for which the subtotals should be calculated. In this example, the SUBTOTAL function will be calculated only for column "D" (with Column.Index equal to 3).
  3. Call the Worksheet.Subtotal method and pass the following parameters: the specified data range to be subtotaled, the index of the column by which you wish to group your data, the specified list of columns to which the subtotals should be added, the code of the function to be used in calculating subtotals and the text to be displayed in the summary rows.

    In this example, a subtotal row will be inserted each time a value in column "B" (with Column.Index equal to 1) changes. The SUM function with code 9 is used to calculate subtotals.

Dim dataRange As Range = worksheet("B3:E23")
' Specify that subtotals should be calculated for the column "D". 
Dim subtotalColumnsList As New List(Of Integer)()
subtotalColumnsList.Add(3)
' Insert subtotals by each change in the column "B" and calculate the SUM fuction for the related rows in the column "D".
worksheet.Subtotal(dataRange, 1, subtotalColumnsList, 9, "Total")
See Also