Sub-Total in Footer for each Page

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:

=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:

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:

=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!