officefileapi-116622-excel-export-library-formulas-how-to-create-subtotals.md
To create a subtotal, call the IXlCell.SetFormula method to add a formula that contains a SUBTOTAL function. To create a formula, use the XlFunc.Subtotal method specifying a summary function as one of the XlSummary enumeration values.
The code snippet below creates SUM subtotals which include values in the hidden rows.
// Create the grand total row.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Grand Total";
cell.ApplyFormatting(totalRowFormatting);
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6));
}
for (int j = 0; j < 5; j++) {
using (IXlCell cell = row.CreateCell()) {
// Use the SUBTOTAL function to calculate grand total sales for each quarter and the entire year.
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, startDataRowForGrandTotal, j + 1, row.RowIndex - 1), XlSummary.Sum, false));
cell.ApplyFormatting(totalRowFormatting);
}
}
}
' Create the grand total row.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = "Grand Total"
cell.ApplyFormatting(totalRowFormatting)
cell.Formatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.6))
End Using
For j As Integer = 0 To 4
Using cell As IXlCell = row.CreateCell()
' Use the SUBTOTAL function to calculate grand total sales for each quarter and the entire year.
cell.SetFormula(XlFunc.Subtotal(XlCellRange.FromLTRB(j + 1, startDataRowForGrandTotal, j + 1, row.RowIndex - 1), XlSummary.Sum, False))
cell.ApplyFormatting(totalRowFormatting)
End Using
Next j
End Using