Pivot Table Defaults to Sum or Count

When you add a numeric field to the Values area of a pivot table, it usually appears as a Sum. For example, in the pivot table shown below, the Units field became Sum of Units.

ValueSum

Occasionally, when you add a numeric field to the Values area, it shows the Count, instead of the Sum. In the same pivot table, when the TotalSales field is added, it shows a Count.

ValueCount

Pivot Table Default Function

If you add a number field to the Values area, the default summary function is Sum. However, if the source data has blank cells, or nonnumeric data, such as text or errors, in that field, the Count function is the default summary function.

You can’t change the default settings for the data fields, but you can manually change the summary function after you add the fields to the Values area.

To manually change the summary function:

  1. Right-click a cell in the field you want to change, and click Summarize Data By.
  2. Click the summary function that you want to use.

SummarizeDataBy

Pivot Table Add-in

There’s a feature that changes all the data fields to SUM, or any other summary function, in my pivot table add-in. It add a new tab to the Ribbon, with time-saving commands that you can use.

  1. After you install the add-in, select any cell in the pivot table.
  2. Then, on the Ribbon’s Pivot Power tab, click SUM ALL.
  3. Or, click Change ALL to, then click the Summary function that you want to use.

sumselectedvalues

______________

For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

_________________

15 thoughts on “Pivot Table Defaults to Sum or Count”

  1. Can you give me a guide on how to complete this is Excel 2007, it seems the instructions are for 2003 unless I am missing something. I have added the add in but it has made no difference. My issue is that when I create the pivot table when I tick the box to add numerical data it is adding to the row labels and not the values, I am having to then drag from the list to the values and it is seeing the data in count and not sum and then I am having to manually change the field settings for each column which is rather time consuming. Please can you help me and advise my best way forward.

    Thanks
    Clare

  2. Clare, the instructions are for Excel 2007, so it should match your version.
    Are there blank cells in your numerical data column, or text cells in that column? That could make the field default to COUNT, instead of SUM.

  3. No all the numerical field have only figures in apart from the title. Refreshed the data and still will not accept the forecast days in values and wants to add to row labels.

  4. very useful! thanks!

    do you have any add-in or help on dragging multiple columns (say like 50 or more) into the data field? default is to drag columns one by one and it’s very time consuming.

    thanks again.

  5. Can you give me a guide on how to complete this is Excel 2010, it seems the instructions are for 2003 unless I am missing something. I have added the add in but it has made no difference. My issue is that when I create the pivot table when I tick the box to add numerical data it is adding to the row labels and not the values, I am having to then drag from the list to the values and it is seeing the data in count and not sum and then I am having to manually change the field settings for each column which is rather time consuming. Please can you help me and advise my best way forward.
    Thanks
    LOKESHKUMAR A V IFB APL BANGALORE

  6. I was trying to understand how to have the names of the column headings as in the data source,but I failed to find that explanation. What is the problem? Each time you have to choose how the values from data source to be executed the program is adding to the original name the function name: “Sum of…”, “Count of …”, Product of …”. Any attempt to delete the unneccessary preface is giving me an error, that the “PivotTable field name already exists.”
    Another point – in blank fields in the raw labels area I recieve the “(blank)” “explanation” text! Why should I need it? In the source excel sheet it is left blank and that is the way to be presented in the PivotTable. It is not allowed to delete it. Again an error is displayed: ” Cannot enter a null value as an item or field name in a PivotTable report”.
    For me it seems strange different data or value interpretation in Excel sheet and PivotTable.

    That was not the case in the old versions of Excel. My current version is 2010.

    All above might have simple solutions, but not intuitive enough. Why should one apply so many editing efforts when trying to use PivotTable to facilitate data presentation if already collected and editted to a certain level in Excel?

    Best regards

    Rumen

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.