Grand total a pivot table column of minimum values

bvallanc
bvallanc used Ask the Experts™
on
How do I get Excel 2007 to sum a column of minimum values in the Grand Total line of a pivot table?  I have attached an image of the sample pivot table I'm working with.  I'd like to total the two columns whose labels start with "Min" in the Grand Total line.  Is this possible?  Currently the Grand Total line returns the minimum value for each of the "Min" columns.  I want the sum of the values that appear in each of the "Min" columns to appear in the Grand Total line of the pivot table.
Sample-Pivot-Table.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
bvallanc,

To do this, you will need to use VBA code.  The following appears to be working for me, assuming you place it on the sheet module for the worksheet containing the PivotTable.

Note that I am assuming the following:

1) You must turn off grand totals for columns in your PT design
2) Do not place anything else underneath that PT

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    
    Dim PT_Rng As Range
    Dim PT_Rows As Long
    
    Set PT_Rng = Target.TableRange1
    PT_Rows = PT_Rng.Rows.Count
    
    With Target.Parent
        .Range(PT_Rng.Cells(1, 1).Offset(PT_Rows, 0), .Cells(.Rows.Count, 1)).EntireRow.Delete
    End With
    
    PT_Rng.Cells(1, 1).Offset(PT_Rows, 0) = "GrandTotal"
    PT_Rng.Cells(1, 1).Offset(PT_Rows, 1).Formula = "=SUM(" & Target.DataBodyRange.Columns(1).Address & ")"
    PT_Rng.Cells(1, 1).Offset(PT_Rows, 2).Formula = "=SUM(" & Target.DataBodyRange.Columns(2).Address & ")"
    PT_Rng.Cells(1, 1).Offset(PT_Rows, 3).Formula = "=SUM(" & Target.DataBodyRange.Columns(3).Address & ")"
    
End Sub

Open in new window


Patrick
Top Expert 2010
Commented:
You might also be interested in this article, which shows how to ensure your PTs get updated automatically:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html

Author

Commented:
Is there a reason why I can't place anything beneath the pivot table?  Doesn't the SUB return Target as the pivot table that's being edited?  I'd like to place multiple pivot tables beneath each other if that's possible.

Thanks,

Bill Vallance
Top Expert 2010

Commented:
Bill,

It's because, as the PT updates, if it grows or shrinks the row I write the totals to will change.

In any event, any time a PT can increase in rows over time, it is a bad idea to put anything under it.

Patrick

Author

Commented:
Excellent response to what I thought was something simple but turned out to be more complictaed than I expected!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial