Pivot Table - Change Function in Data 1

[shepkrm]

I am creating a Pivot Table with multiple data fields. However, whenever I drag a field to the table, it defaults to "Count." I am attempting to get "Sum" instead.

I can change the function when there is only one field in the data area. However, how do I change the functions of multiple data fields?

Thanks in advance for your help!
-Becky

 

[SkipVought]

Hi,

Each field that you drag into the DATA area can be individually changed to whatever aggregation function you wish.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[shepkrm]

Skip,

Thanks...how would I do this?

-Becky

 

[SkipVought]

Right click on the Field and choose your aggregation function.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[SkipVought]

Field Settings


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[shepkrm]

So easy, yet so difficult.

Thanks so much!!!

 

[KenWright]

I know this isn't the VBA forum, but if you wanted to automate this for a number of them at a time, then assign these bits of code to icons on your toolbar, or to keyboard shortcuts.

Sub MakeThemAllSum()
    Application.ScreenUpdating = False
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim s As String
    Dim n As Long
    
    For n = 1 To ActiveSheet.PivotTables.Count
        For Each pf In ActiveSheet.PivotTables(n).DataFields
            pf.Function = xlSum
        Next pf
    Next n

    Application.ScreenUpdating = True
End Sub

Sub MakeThemAllCount()
    Application.ScreenUpdating = False
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim s As String
    Dim n As Long

    For n = 1 To ActiveSheet.PivotTables.Count
        For Each pf In ActiveSheet.PivotTables(n).DataFields
            pf.Function = xlCount
        Next pf
    Next n

    Application.ScreenUpdating = True
End Sub

I use an addin that I put together to do all this kind of stuff, because I hate having to do it manually. Another example would be formatting the data into various currencies. There is one report that I produce for every proposal run we do where I end up pulling a dozen different fields, so with the addin I just have to have a couple of clicks and it will instantly format all the fields to SUM and in the currency of my choice.

Makes for a lot less work.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Gavona]

I'll ceetainly add those procedures to my repertoire. Thanks Ken.

Thanks,

Gavin