As you work with PivotTables in Excel, sometimes you may need to change the summary function from the “Count” function that counts the number of non-empty values in a field to the “Sum” function that computes the sum of the values.
For example, suppose you have a PivotTable that shows the number of expenses incurred by each department.
In that case, you may want to change the aggregation function from “Count” to “Sum” to see the total amount of money each department spends.
This tutorial shows four techniques for changing the summary function from “Count” to “Sum” in Excel Pivot Table.
Method #1: Using Pivot Table Fields Task Pane
You can change Count to Sum in Excel Pivot Table using the PivotTable Fields task pane.
The task pane provides an efficient way to access the ‘Value Field Settings’ dialog box that we can use to change the count summary to the sum summary in the Pivot Table.
Let’s consider the following Pivot Table showing the count or number of sales made by various salespeople on particular dates:
We want to change the Count summary function to Sum in the Pivot Table to see the total sales the salespeople made on each day.
We use the following steps:
- Select any cell on the Pivot Table to activate the PivotTable Fields task pane on the right of the Excel window.
Notice the Count of SALES button on the Values box at the bottom of the Pivot Table Fields task pane.
Note: If the PivotTable Fields task pane does not show when you select the Pivot Table, you can activate it by clicking the Field List button on the Show group of the PivotTable Analyze tab.
- On the Values box, click the down arrow on the Count of SALES button and click the Value Field Settings option on the list that appears to open the Value Field Settings dialog box.
Note: Alternatively, you can launch the Value Field Settings dialog box by right-clicking any cell on the Grand Total column and choosing the Value Field Settings option on the shortcut menu:
- On the Value Field Settings dialog box, select Sum on the Summarize value field by list box and click OK.
Your Pivot Table will now display the sum of sales instead of the count of sales, as depicted below:
Also read: Cannot Group That Selection Error in Pivot Tables
Method #2: Using Value Field’s Context Menu
We can use the value field’s shortcut or context menu to change the count summary to the sum summary in Excel Pivot Table.
Suppose we have the following Pivot Table showing the count of sales made by a particular company on different dates:
We want to use the shortcut menu to change the count summary to the sum summary on the Pivot Table.
We use the below steps:
- Right-click any cell on the Grand Total field, hover the cursor over the Summarize Values By option, and select Sum on the submenu:
The Pivot Table will now display the sum of sales instead of the count of sales, as shown below:
Also read: Pivot Table Field Name is Not Valid – How to Fix?
Method #3: Using the Ribbon to Change Count to Sum in Excel Pivot Table
We can use the Excel Ribbon to access the Value Field Settings dialog box that we can use to change Count to Sum in Excel Pivot Table.
We have the following Pivot Table showing the sales made by a particular company summarized by count.
We want to change the summary to sum using the Ribbon using the following steps:
- Select any cell on the Grand Total field of the Pivot Table to activate the contextual PivotTable Analyze tab on the Ribbon shown below:
- On the PivotTable Analyze tab, click the Field Settings button on the Active Field group.
- On the Value Field Settings dialog box, select Sum on the Summarize value field by list box and click OK.
Your Pivot Table will now show the sum of sales instead of the count of sales, as shown below:
Also read: Remove Grand Total From Pivot Table in Excel
Method #4: Using Excel VBA to Change Count to Sum in Excel PivotTable
We can use Excel VBA to change the count summary to the sum summary in Excel Pivot Table. This method is convenient if you want to change the summary in many fields.
We have the following Pivot Table showing the number of sales made by a specific company on various dates:
We want to use Excel VBA to change the count summary to the sum summary in the Pivot Table.
We use the following steps:
- Press the shortcut Alt + F11 to launch the Visual Basic Editor.
- Open the Insert menu and choose Module to insert a module in the Visual Basic Editor.
- Copy the following Excel VBA code and paste it into the module:
For those not very well versed with VBA, I have added a note (that starts with ‘) before each line that explains what that line of code does.
' Start a VBA subroutine called SumValueFields
Sub SumValueFields()
' Declare ptb as an object variable that represents a PivotTable
Dim ptb As PivotTable
' Declare pfd as an object variable that represents a PivotField
Dim pfd As PivotField
' Declare wsh as an object variable that represents a Worksheet
Dim wsh As Worksheet
' Set wsh to represent the active (currently selected) worksheet in Excel
Set wsh = ActiveSheet
' Set ptb to represent the first PivotTable on the active worksheet
Set ptb = wsh.PivotTables(1)
' Turn off screen updating to speed up code execution
Application.ScreenUpdating = False
' Turn on ManualUpdate to prevent PivotTable from updating with each change
ptb.ManualUpdate = True
' Start a loop that goes through each PivotField in the PivotTable's data fields
For Each pfd In ptb.DataFields
' Change the function of the current PivotField to Sum
pfd.Function = xlSum
' Move to the next PivotField
Next pfd
' Turn off ManualUpdate to allow PivotTable to update after changes are made
ptb.ManualUpdate = False
' Turn screen updating back on to show changes
Application.ScreenUpdating = True
' Remove the reference to the PivotField object to free up system memory
Set pfd = Nothing
' Remove the reference to the PivotTable object to free up system memory
Set ptb = Nothing
' Remove the reference to the Worksheet object to free up system memory
Set wsh = Nothing
' End the subroutine
End Sub - Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).
- Press the shortcut Alt + F11 to switch to the active worksheet containing the PivotTable .
- Press Alt + F8 to open the Macro dialog box, select the SumValueFields macro on the macro list, and click Run.
The sub-procedure is executed and changes the count summary to count summary in the Pivot Table as shown below:
Note: The advantages of using Excel VBA to change the count to sum in Excel Pivot Table include the following:
- Efficiency: If you have a large Pivot Table with many rows and columns, manually changing the aggregation function from count to sum can be time-consuming. With Excel VBA, you can change the aggregation function for many PivotTables with just a few lines of code.
- Flexibility: Excel VBA allows you to customize the aggregation function. For example, if you want to exclude specific rows or columns from the aggregation, you can easily make these modifications in the code.
- Reusability: Once you’ve written the Excel VBA code to change the aggregation function, you can save it and reuse it for future Pivot Tables. This reusability can save you time and effort in the long run.
This tutorial has explained four techniques for changing count to sum in Excel Pivot Table. We hope you found the tutorial helpful.
Other Excel articles you may also like:
