Microsoft Excel

Sum, Average, Count, Min, Max, and More

So far, every example in this tutorial has involved summing data. It is also possible to get an average, minimum, or maximum of data. In VBA, change the Function 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:

```    ' 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

With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlAverage
.Position = 3
.NumberFormat = "#,##0"
.Name = "Average Revenue"
End With

With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlMin
.Position = 4
.NumberFormat = "#,##0"
.Name = "Smallest Order"
End With

With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlMax
.Position = 5
.NumberFormat = "#,##0"
.Name = "Largest Order"
End With
```

The resultant pivot table provides a number of statistics about the average revenue, largest order, smallest order, and so on.

by updated