Excel Magic Trick 1012: Automatically Add All Subtotals In A Column (6 Methods)

See 6 methods to add all the subtotals in a column:
1. Manually with the SUM function
2. SUM function divided by 2 SUM/2
3. Array Formulas that uses SUMPRODUCT and the New Excel 2013 function ISFORMULA
4. SUBTOTAL if subtotals were created with SUBTOTAL function
5. AGGREGATE if subtotals were created with SUBTOTAL function
6. Subtotal feature

[excelisfun]

You are welcome. To return the favor you can click the Like. If you can afford it, you can buy a book or DVD to support the cause.

Many more videos to come!

[robertdavies]

Mike, once again your videos have changed my life, my management accounts look so good with your help. The subtotal video is so helpful to me. Thanks again, I love slaying Dragons and can't wait for Ctrl Shift Enter Bob

[prkshah]

It helped to brush up after 16 years. Thanks

[ExcelStrategy]

The solution with the ISFORMULA inside the SUMPRODUCT has no match ! It is so elegant that it simply annihilates all the others !!

[krn]

Thanks Mike. Love the "isformula" option. Pretty cool.

[elphau]

this trick is awesome to me. glad to find out. thank you!

[excelisfun]

all the videos about array formulas at YouTube, which is 100s, are all teasers for the book. I have a few more coming out this week that are teasers.

[AnandPenmatcha]

I Always use old accountant trick being accountant. thanks for posting the alternatives.
from now onwards i will use the arrayformula so that it will be difficult to understand and change formulas.
i am still waiting for the Ctrl+shift+enter
do you have teaser for this book

[ashwani]

Well, your formula not visible on my cell. Plz make videos in zoom to enable the viewers can see and understand. Thanks

[SalWick-]

I have long random data in a column and i want excel to subtotal every time when the sum of the number above is 150, is there any formula to do this function.

[excelisfun]

When you do what you love, 16 hour days seem quick...

[salamamohammad]

thanks a lot,  what if we need to add subtotal beside the grand total per each printed page of some table that has a more than 200 rows of numbers in a certain column.
i found this VBA code on-line, but couldn't administer it, do you mind if you clear the VBA  approach for us .
Sub printwithtotals()
    'fill in this information
    FirstDataRow = 6
    RowsPerPage = 40
    ColToTotal = 5 'column e is th 5th column...

    'find how many rows today
    headrow = FirstDataRow - 1
    PageCount = (FinalRow - headrow) / RowsPerPage
    PageCount = Application.WorksheetFunction.RoundUp(PageCount, 0)

    For i = 1 To PageCount
        ThisPageFirstRow = (i - 1) * RowsPerPage + headrow + 1
        thispagelastrow = ThisPageFirstRow + RowsPerPage - 1
        TotalThisPage =
            Sum (Cells(ThisPageFirstRow, ColToTotal).Resize(RowsPerPage, 1))
    TotalAllPages =
            Sum (Cells(FirstDataRow, * i, 1))
        
    ' change the footer for this page
    = False
    With ActiveSheet.PageSetup
        .LeftFooter = "Total This Page $" & Format(TotalThisPage, "#, ##0.00")
        .RightFooter = "Total Through This Page $" & Format(TotalAllPges, "#, ##0.00")
    End With
    = True
    
    ' print this page
    From:=i, To:=i, Copies:=i, Collate _
        :=True, IgnorePrintAreas:=False
      
    Next i
    
    ' Clear the footer in case someone prints without the macro
      = False
      With ActiveSheets.PageSetup
         .LeftFooter = " Use printwithouttotals macro"
         .RightFooter = ""
      End With
      = True
End Sub

[fornepal]

HOW TO ADD ALL THE SUB TOTALS FROM BOTH TABLES AT ONCE TO GET THE GRAND TOTAL