Sub-Total in Footer for each Page

Copper Contributor

My Problem: -

Excel Version 2016
Firstly, apologies if I have not adhered to any of the Forum rules, it is my first post.

I want a sum total (in a footer) for each page. Pretty simple I thought until I discovered excel do not provide the function to do this i.e. do sum totals in footers Grrrrrr!!!!

We produce ‘Schedules of Works’ with the furthest most right-hand column (this being ‘F’) used as a pricing column for contractors to enter their prices alongside our work descriptions. These prices / costs should then total at the bottom for each page.

Each blank page roughly starts off with 50 rows, so, I could just put a sum total in row 51. However, as we write the schedule, this could change to as little as 10 rows per page dependent upon the size of the descriptions we write in each row. As such the sum-total would be pushed off the bottom and onto the next page.

This wouldn’t be too bad for our Admin to re-configure if it were only a few pages, however each schedule can be up to 80 pages long, times that by 20 or so people in the office producing many schedules. This now becomes a lot of editing work.

Not one to let this defeat me, I have spent the last week Googling a hopeful answer from many excel forums / pod cast videos etc as follows: -

1st Try: - (using the formula bar)

=IF(MOD(ROW(),45)=5,SUM(G&6:G6),””)

This works fine to a degree however with the following problems for me: -
1) – If I need to insert 5 or more rows it pushes the sub-total onto the next page
2) – If I the row height increase through needing to enter a large description / amount of txt it pushes the sub-total onto the next page
3) The sub-total isn’t in a footer anyway, just at the bottom of the page. Although maybe it could be referenced in the footer via a macro or something.

2nd Try: - (using a macro)

 

Sub PrintWithTotals()

Fill in this information
FirstDataRow = 6
RowsPerPage = 50
ColToTotal = 6

‘ Find how many rows today
HeadRow = FirstDataRow - 1
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
PageCount = (FinalRow - HeadRow) / RowsPerPage
PageCount = Application.WorksheetFunction.RoundUp(PageCount, 0)

DOESN'T STEP THROUGH THE NEXT BIT
-------------------------------------------------------------------------------------------

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))


‘ Change the Footer for this page
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.RightFooter = "Total This Page $" & Format(TotalThisPage, "#,##0.00")
End With
Application.PrintCommunication = True

' Print this page
ActiveWindow.SelectedSheets.PrintOut From:=i, To:=i, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False[/B][/U]

------------------------------------------------------------------------------------------------
STEPS INTO THE FOLLOWING, MISSES OUT THE ABOVE

Next i

‘ Clear the footer in case someone prints without the macro 
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftFooter = "Use PrintWithTotals Macro "
.RightFooter = " "
End With
Application.PrintCommunication = True

End Sub


This dosn't produc any errors, nevertheless it doesn’t work. When I step through the code either using F8 or the debug step-in procedure it misses out the section I have noted above. So I'm guessing it isn't running this part properly.

Watching the video from MrExcel, he steps through it fine and it works fine.

To re-cap, I am just after getting a sub-total (for each page) in a footer regardless of how many rows I have for that page, delete, add or increase in size.

I am at a loss what else to try. If anyone could please help, the winner gets a Big Hug!

Cheers Steve

3 Replies

having same issue had you got the solution @stevetroughton10 

@stevetroughton10 

I'd be glad to help you with the Excel issue of calculating subtotals for each page in a footer, even with varying row counts and descriptions. Here are some approaches that address the challenges you mentioned:

1. Dynamic Formula with Conditional Formatting:

This method combines a formula and conditional formatting to automatically adjust the subtotal's position and visibility based on the number of rows.

Formula:

Excel
=IF(MOD(ROW()-ROW(B1),45)=0,SUM(G2:G&ROW()-ROW(B1)),"")
 

Explanation:

  • MOD(ROW()-ROW(B1),45)=0: This checks if the current row number is divisible by 45 (adjustable based on your typical page size).
  • SUM(G2:G&ROW()-ROW(B1)): If the condition is true, this calculates the sum of column G from row 2 to the current row.
  • ROW(B1) is a reference to the first row with data (adjust if needed).

Conditional Formatting:

  • Apply conditional formatting to the cell containing the formula.
  • Set the rule to format the cell as hidden (e.g., white font on white background) when the formula result is empty.

2. Macro with Footer Update:

This approach uses a macro to calculate the subtotal for each page and update the footer dynamically.

Macro:

VBA
Sub UpdateFooters()
Dim pageCount As Integer, currentPage As Integer, firstRow As Integer, lastRow As Integer
Dim total As Double

pageCount = Application.WorksheetFunction.RoundUp(Rows.Count / 45, 0) ' Adjust divisor if needed

For currentPage = 1 To pageCount
    firstRow = (currentPage - 1) * 45 + 1 ' Adjust base row if needed
    lastRow = firstRow + 44 ' Adjust offset if needed
    total = Application.WorksheetFunction.Sum(Range("G" & firstRow & ":G" & lastRow))

    ' Update footer on current page
    ActiveSheet.PageSetup.Footer.RightFooter = "Total Page " & currentPage & ": $" & Format(total, "#,##0.00")
Next currentPage

End Sub
 

Explanation:

  • The macro loops through each page, calculates the subtotal using the specified range, and updates the right footer with the total and page number.

3. Advanced Formula with Table and Named Ranges:

This method uses a more advanced formula with a table and named ranges for flexibility and easier customization.

Steps:

  1. Create a table encompassing your data (Ctrl+T).
  2. Define named ranges:
    • DataRange: The entire table (e.g., Table1).
    • RowsPerPage: The typical number of rows per page (e.g., 45).
  3. Use this formula in the desired footer cell:
Excel
=SUM(IF(MOD(ROW(DataRange)-ROW(DataRange[[#Headers],[Price]]),RowsPerPage)=0,DataRange[[Price]:[Price]],""))
 

Explanation:

  • The formula sums values in the "Price" column (adjust if needed) based on the condition of being on the last row of a page (adjustable).

Additional Tips:

  • Consider using a combination of methods for more robust or customized solutions.
  • Thoroughly test any formulas or macros before implementing them in your actual spreadsheets.
  • If you encounter specific errors or need further assistance, provide more details about your spreadsheet setup and the issues you're facing.

I hope these improved approaches help you achieve the desired subtotals and footers in your Excel workbook!