Change Count to Sum in Pivot Table

[sabrina0034]

Hi All - I have a pivot table with many, many columns, and each time I make the pivot table, all the data within the columns comes through as 'count' even though the data itself is numbers in number format. So what happens is that I have to manually change each column to 'sum' by double-clicking on the column header and manually selecting 'sum' for each one!

Does anyone know how to simultaneously change all columns to 'sum', in one shot?

Don't know if it matters, but I'm working in Excel 2007.

Thanks in advance,
Sabrina

 

[DonkeyOte]

You could use some basic code to update the Pivot fields, eg:

Sub SET_PTFIELDS_TO_SUM()
    Dim PT As PivotTable, ptField As PivotField
    Set PT = ActiveSheet.PivotTables(1)
    For Each ptField In PT.DataFields
        ptField.Function = xlSum
    Next ptField
    Set PT = Nothing
End Sub

Modify that in red to reflect your set up (so as to fire against the appropriate Pivot table)

 

[DonkeyOte]

If you're able to install add-ins see Debra Dalgleish's Pivot Add-In