Sum Average Count Min Max and More

So far, every example in this chapter has involved summing data. It is also possible to get an average, minimum, or maximum of data. In VBA, change the xlFunction property of the data field and give the data field a unique name. For example, the following code fragment produces five different summaries of the quantity field, each with a unique name:

Sub ReportManyDetailsByCustomer()

1 Show Sum, Count, Average, Min, Max Revenue by Customer

Dim WSD As Worksheet Dim PTCache As PivotCache Dim PT As PivotTable Dim PRange As Range Dim FinalRow As Long

Set WSD = Worksheets("Pivot Table")

1 Delete any prior pivot tables For Each PT In WSD.PivotTables

PT.TableRange2.Clear Next PT

1 Define input area and set up a pivot cache FinalRow = WSD.Cells(65536, 1).End(xlUp).Row Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8)

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range("J2"),

TableName:="PivotTable1") PT.ManualUpdate = True 1 Set up the row fields

PT.AddFields RowFields:="Customer", ColumnFields:="Data"

1 Set up the data fields With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0,K" .Name = "Total Revenue" End With

With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlCount .Position = 2 .NumberFormat = "#,##0" .Name = "Number Orders" End With

0 0

Post a comment