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
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!
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
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.
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 = Application.WorksheetFunction_ Sum (Cells(ThisPageFirstRow, ColToTotal).Resize(RowsPerPage, 1)) TotalAllPages = Application.WorksheetFunction_ Sum (Cells(FirstDataRow, ColToTotal).Resize(RowsPerPage * i, 1)) ' change the footer for this page Application.PrintCommunication = False With ActiveSheet.PageSetup .LeftFooter = "Total This Page $" & Format(TotalThisPage, "#,##0.00") .RightFooter = "Total Through This Page $" & Format(TotalAllPges, "#,##0.00") End With Application.PrintCommunication = True ' print this page ActiveWindow.SelectedSheets.PrintOut From:=i, To:=i, Copies:=i, Collate _ :=True, IgnorePrintAreas:=False Next i ' Clear the footer in case someone prints without the macro Application.PrintCommunication = False With ActiveSheets.PageSetup .LeftFooter = " Use printwithouttotals macro" .RightFooter = "" End With Application.PrintCommunication = True End Sub
Thank you very much, sir
Glad this helps, Provider!!!!!
Glad you like it!
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
Thanks, ExcelStrategy!
so helpful thank you
Glad it helps!
The solution with the ISFORMULA inside the SUMPRODUCT has no match ! It is so elegant that it simply annihilates all the others !!
Thanks Mike. Love the "isformula" option. Pretty cool.
It helped to brush up after 16 years. Thanks
this trick is awesome to me. glad to find out. thank you!
all the videos about array formulas at UA-cam, which is 100s, are all teasers for the book. I have a few more coming out this week that are teasers.
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!
When you do what you love, 16 hour days seem quick...
Very helpful , thanks
Love your work !
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
1:12 4:26 5:50
Well, your formula not visible on my cell. Plz make videos in zoom to enable the viewers can see and understand. Thanks
Always best
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.
HOW TO ADD ALL THE SUB TOTALS FROM BOTH TABLES AT ONCE TO GET THE GRAND TOTAL
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 = Application.WorksheetFunction_
Sum (Cells(ThisPageFirstRow, ColToTotal).Resize(RowsPerPage, 1))
TotalAllPages = Application.WorksheetFunction_
Sum (Cells(FirstDataRow, ColToTotal).Resize(RowsPerPage * i, 1))
' change the footer for this page
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftFooter = "Total This Page $" & Format(TotalThisPage, "#,##0.00")
.RightFooter = "Total Through This Page $" & Format(TotalAllPges, "#,##0.00")
End With
Application.PrintCommunication = True
' print this page
ActiveWindow.SelectedSheets.PrintOut From:=i, To:=i, Copies:=i, Collate _
:=True, IgnorePrintAreas:=False
Next i
' Clear the footer in case someone prints without the macro
Application.PrintCommunication = False
With ActiveSheets.PageSetup
.LeftFooter = " Use printwithouttotals macro"
.RightFooter = ""
End With
Application.PrintCommunication = True
End Sub
Creativity
gud
Export excel live update to word