Change All Pivot Table Value Fields to SUM

Sometimes when you add fields to the Values area of a pivot table, they appear as Count of Field instead of Sum of Field. In the screen shot below, the Quantity field shows the COUNT, and the TotalPrice field shows the SUM.

pivot table Summary Macro_01

COUNT Summary Function

The COUNT summary function is the default if a field in the pivot table’s source data contains blank cells, or cells with text. For example, in a column of invoice dates, someone might mistype a month name, so it isn’t recognized as a valid date. Or, in a column of sales quantities, you might have typed “N/A”, instead of a number, or maybe you left a blank cell to fill in later.

In these cases, where the source column contains blank cells or text, the summary function defaults to Count; otherwise, it defaults to Sum.

Change the Summary Function

You can manually change the summary function from Count to Sum, after the Values fields have been added. Or, to make things easier, you can run a macro to change the summary function.

This sample macro changes all the Values fields in the first pivot table on the active sheet to use the Sum function (xlSum). The code refers to the pivot table by index number (1), instead of using a specific name (“PivotTable1”), and that makes the macro more flexible.

You can copy this code to a regular code module in your workbook, and run it when you want to change the summary functions for all the Value fields.

Sub SumAllValueFields()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim ws As Worksheet

  Set ws = ActiveSheet
  Set pt = ws.PivotTables(1)
  Application.ScreenUpdating = False

    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = xlSum
    Next pf
    pt.ManualUpdate = False

  Application.ScreenUpdating = True
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
End Sub

Download the Sample File

You can download the sample file with the Change Summary Function to Sum sample code, from my Contextures website.

The zipped file is in xlsm format, and contains macros. You can run the macro in the sample file, or copy the Excel VBA code to a regular code module in another workbook, and run it there.

Pivot Power Add-in

If you’ve bought a copy of my Pivot Power Premium add-in, this feature is included. Just click the commands in the Data Fields group, and you can quickly change all fields to SUM, or any other summary function. Or, select specific value fields, and just change those to a different summary function.



This entry was posted in Calculations. Bookmark the permalink.

13 Responses to Change All Pivot Table Value Fields to SUM

  1. Sayyad says:

    This is very useful. But I have a small variant of the issue: I work in Analytics industry and I usually have 200 variables to add to a pivot table. Is there a VBA code that can add all the variables to the data field?

  2. Stevef says:

    Very helpful- have added to my QAT

  3. Viitaa says:

    Super! Thanks!

  4. Jannie Els says:


    I’ve tried the code, and it works for all functions except for ‘xlCount’ and ‘xlCountNums’… Why would that be?



  5. Paul D says:

    Very helpful. Thank you!!

  6. Vica says:

    This just made my day! thanks for sharing.

  7. Roy says:

    This is great, thank you so much!

  8. Cameron says:

    Four-year-old post and still useful. Nice fifteen second solution. Thanks a ton, Debra!

  9. Martin says:

    A nice one. what about changing function to average??

  10. Alex says:

    It worked!

  11. Craig Reges says:

    Works well. Thanks for posting.

  12. Ryan says:

    This is great, thanks for posting. I’ve made a slight modification to the code so it sums the values of the pivot table you have selected. I’ve replaced
    Set pt = ws.PivotTables(1) with
    Set PT = ActiveCell.PivotTable

  13. Juan C says:

    Thanks a lot

Leave a Reply

Your email address will not be published. Required fields are marked *