My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Excel PivotTable Default to SUM instead of COUNT

You are here: Home / Excel PivotTables / Excel PivotTable Default to SUM instead of COUNT
Excel Tool Belt
by

If you put a field in the values area of a PivotTable and it defaults to COUNT as opposed to SUM, the reason is that the column has at least one cell containing text, or one cell that is empty. Yes, one pesky cell that doesn't contain a number is enough for a PivotTable to return COUNT.

PivotTable default to sum instead of count

It’s annoying but unfortunately the only way to make a PivotTable default to SUM instead of COUNT is to make sure your column of data contains a number in every cell. You see the PivotTable is trying to determine the type of data you have and apply a relevant aggregation function for you. It’s trying to be helpful.

Solution: Right-click a value in the column you want to change > Summarize Values By > Sum:

PivotTable default to sum instead of count

Excel Tool Belt

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel PivotTables Posts

Sorting Excel Date Slicers
Use these tricks to custom sort Excel Slicer buttons enabling them appear in a specific order that suits your needs.
PivotTable Formatting
Create PivotTables that don’t look like PivotTables with these really easy PivotTable Formatting tips and tricks.
Images in Excel Pivot Tables
Include images in Excel PivotTables, combined with some slick formatting, and create dynamic and visually rich PivotTables like never before.
Auto Refresh PivotTables isn’t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.
Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.
There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L
Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.
Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.
Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting is essential because they’re big and chunky. In this tutorial I show you the tricks to make Excel Slicers small.


Category:

Reader Interactions

Comments

  2. Brendan Kelly

    Hi,
    I “Control H” my data where possible…find and replace.
    Find “Blanks” and Replace them with zeroes…that way there are no blanks.
    I hope this helps someone,
    Brendan.

    Reply

Leave a Reply

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

Current ye@r *