My Blog

Creating Subtotals

In continuation from here

What if the above “Grouping” could also show us the Total or the Average amount that we spent in each month?

Either we can follow the above approach to ‘Group’ the data and use Excel’s formula “=Subtotal()” or we use the Excel’s inbuilt feature to create the subtotal automatically

Consider this Example:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    '~~> Add a New Workbook
    xlWorkBook = xlApp.Workbooks.Add

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Set the relebant sheet that we want to work with
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

    With xlWorkSheet
    '~~> Directly type the values that we want
        .Range("A1").Value = "Month" : .Range("B1").Value = "Date" : .Range("C1").Value = "Money Spent"
        .Range("A2:A5").Value = "January" : .Range("A6:A9").Value = "February"
        .Range("A10:A13").Value = "March" : .Range("A14:A17").Value = "April"

        .Range("B2").Value = "1/6/2011" : .Range("B3").Value = "1/10/2011" : .Range("B4").Value = "1/20/2011"
        .Range("B5").Value = "1/31/2011" : .Range("B6").Value = "1/6/2011" : .Range("B7").Value = "1/10/2011"
        .Range("B8").Value = "1/20/2011" : .Range("B9").Value = "1/31/2011" : .Range("B10").Value = "1/6/2011"
        .Range("B11").Value = "1/10/2011" : .Range("B12").Value = "1/20/2011" : .Range("B13").Value = "1/31/2011"
        .Range("B14").Value = "1/6/2011" : .Range("B15").Value = "1/10/2011" : .Range("B16").Value = "1/20/2011"
        .Range("B17").Value = "1/31/2011"

        .Range("C2").Value = "200" : .Range("C3").Value = "350" : .Range("C4").Value = "155"
        .Range("C5").Value = "345" : .Range("C6").Value = "213" : .Range("C7").Value = "231"
        .Range("C8").Value = "321" : .Range("C9").Value = "341" : .Range("C10").Value = "231"
        .Range("C11").Value = "345" : .Range("C12").Value = "432" : .Range("C13").Value = "124"
        .Range("C14").Value = "543" : .Range("C15").Value = "342" : .Range("C16").Value = "222"
        .Range("C17").Value = "215"

        '~~> Creating the Subtotal. {3} below is the number of columns i.e from A to C
        '~~> To find the vaerage use xlAverage instead of xlSum
        .Range("A1:C17").Subtotal(GroupBy:=1, Function:=Excel.XlConsolidationFunction.xlSum, TotalList:=New Int32() {3}, _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True)

        '~~> Autofitting text in columns
        .Columns("A:C").EntireColumn.AutoFit()
    End With
End Sub

This is the output that you will get.