How to sum, count and modify value fields in pivot tables

Key points of this article

The default summarization method of the value field of the PivotTable report is determined by the source data: numeric columns are summed by default, and columns containing text or blanks are counted by default.
To change the summary method, select Sum, Count or Average in the value field settings, which only affects the current field.
There is no unified batch entry for multiple value fields. They should be set one by one, or the same field should be repeatedly dragged into different summary methods.
The value display method (such as percentage of the total) and the summary method are two different things. Make sure which one you want before changing it.
Saving numbers as text will cause the sum to change to count. Check the number format of the source data before changing the summary method.

After the pivot table is completed, we often encounter a problem: we clearly want to calculate the sum, but the result shows a count; or a column of sales should be calculated as an average, but it is calculated as a sum. Many people think that they need to redo the pivot table. In fact, they only need to enter the value field settings and change the summary method.

This article is written for people who often use Excel to do statistics. It is also suitable for readers who need to organize tables in PDF or other formats into data and then summarize them. After reading, you can judge why the value field is displayed as it is now, know how to change it to sum, count or average, how to deal with multiple fields, and how to check whether the result is correct after changing it.

How to sum, count or change a pivot table to an average? One sentence conclusion

First look at the source data, and then enter the value field settings to select the summary method. The pivot table will give default values based on the type of source data. You can modify them manually if necessary without rebuilding the table.

your situation	Recommended practices	reason
Numerical column, want to see the sum	Keep the default sum, or select sum in the value field settings	By default, numerical columns are summed, directly
Want to count the number of occurrences, such as the number of orders	Select count	Counting counts the number of non-empty cells, regardless of the size of the value.
I want to see the average level of each group, such as the average customer price	Choose average	The mean is equal to the sum divided by the count and is suitable for comparing levels between groups
The same field needs both sum and count	Drag the field into the value area repeatedly, and set a summary method for each	A value field can only have one summary method. Repeated dragging is a common method.
The original summation shows the count	Check whether the source column is mixed with text numbers or blanks, and refresh after correction	As long as there is text in the source column, Excel will count it by default.

Before you start: Confirm these points first

Before you make any changes, make sure to judge a few things, otherwise you may get incorrect results if you make changes.

The numbers in the source data are not real numbers. Numbers copied from web pages, PDFs or other software often exist in text form and may have a small green triangle in the upper left corner. Text numbers are treated as non-numeric values in pivot tables and become counts by default. Select the column, convert the text to numbers, and then refresh the pivot table.
There are no blanks or outliers in this column. Blank cells will be ignored when counting, but if a column is mostly blank, the counting result will look small and it is easy to misjudge.
Do you want summary mode or value display mode. The summary method determines how each grid is calculated, such as sum, count, and average; the value display method determines how the results are presented, such as percentage of the total, and chain ratio. The two have different positions in the value field settings. If they are confused, unexpected numbers will be obtained.
Can the source of the table be used directly? If the original data is in PDF, you can first use OmniDoc's PDF editor to export it to an Excel file, or do OCR on the scanned version first, then organize it into a standard table structure, and then create a pivot table.
Whether the data will be updated. Modifying the summary method will not automatically synchronize changes in source data. After adding or subtracting rows to the source data, you need to refresh the pivot table, otherwise the results will still be old.

Steps to modify how value fields are summarized in Excel

Open the workbook containing the pivot table, click any cell in the pivot table, and confirm that the pivot table is selected.
Find the field you want to modify in the value area, and right-click on any numerical cell in the field.
Find the entry for value field settings in the right-click menu and open the dialog box.
Select the required options such as sum, count, and average in the summary method list.
If you want to display a new name in the pivot table, modify it in the custom name. Do not repeat the name with other fields.
Click OK and observe whether the value of the field in the pivot table changes as expected.
If there are additions, deletions, or modifications to the source data, return to the pivot table, perform a refresh, and confirm that the results are consistent with the source data.

How to batch modify multiple value fields

Excel's value field settings are performed field by field, and there is no way to uniformly change the summary method by selecting multiple fields at once. If there are five or six value fields in the pivot table that need to be changed, it is recommended to proceed as follows.

Modify one by one. When there are not many fields, follow the above steps to set them directly, and check them one by one after making changes.
Multiple summaries of the same field. When you need to see the sum and count at the same time, drag the field into the value area again, then set the summary method of the two value fields respectively, and give them different names to avoid confusion.
Reorganize fields. If the value fields of the current pivot table are too confusing, you can first remove the unnecessary fields from the value area, then drag them in again as needed, and set the summary method one by one. This approach does not change the source data.
Unified source data format. If the problem in multiple fields comes from text numbers, the trouble-free way is to uniformly convert the format in the source table first, and then refresh the pivot table instead of modifying each field one by one.

Check each item after completion

After changing the summary method, don't deliver directly; at least check the following items.

Inspection items	How to see it	What should you do if there's a problem?
Summary method	Check whether the identifier next to the value field name matches your settings	Reopen the value field settings and confirm that the target field is selected
Total figures	Pick any group, manually filter or calculate it with a formula, and compare it with a pivot table	If inconsistent, check whether the source column contains text numbers or duplicate rows
Counting the results	The count should equal the number of non-empty records in the group	If the column is too small, check whether the source column has many blanks or if the fields are misplaced
Average	The mean should be equal to the sum of the same group divided by the same group count	If the deviation is large, check whether there are values that are abnormally large or small, and confirm that the source data is correct
Refreshed results	After refreshing the pivot table, does the number change with the source data?	If there are no changes, check whether the source data range contains any newly added rows

Tables obtained by machine translation or OCR may have recognition errors in numbers and names, so important data must be checked against the original files before calculation.

📊 Core Comparison

Many people mix summarization methods with value display methods; the comparison below can help distinguish them.

Comparison items	Summary method	Value display method
The content of the decision	What is used to calculate each grid, such as sum, count, or average?	What form are the calculation results presented, such as percentages or differences?
Common options	Summing, counting, average, large, small, etc	Percentage of the total, percentage of the column total, differences from the previous period, etc
Suitable questions	How much, how much in total, how much on average	What is the proportion, and how much has it changed compared to the previous period?
Set the position	Summary method options for setting value fields	Value field settings, a separate display option for display modes

🎯 Which method suits you?

If you just want to change the numbers from summing to counting, or the average value into summing, just modify the summation method. If your spreadsheet comes from a complex source, such as data from PDFs, scans, or other software-exported files, you need to first handle the format and then create a pivot table. If you need to compare multiple summary methods simultaneously, such as summing, counting, and averaging each group, you should reserve a value field for each requirement and name it separately.

❓ Frequently Asked Questions (FAQ)

1. Why does a pivot table display counts by default instead of summing?

This is usually because the source data column contains text, blanks, or text numbers copied from elsewhere. As long as the column is not all numeric values, Excel will count by default. Convert text numbers to numbers, then refresh the pivot table, and usually sum up again.

2. How do you count the count in a pivot table?

Drag any unique field, such as order number or student ID, into the value area, then select Count in the value field settings. Counting counts the number of non-empty records, which is suitable for counting the number of occurrences.

3. If I change the summary method, will the original table change?

No. The summary method only affects the calculation results of pivot tables; the source data remains unchanged.

4. Can the value fields in a pivot table be directly changed to averages?

Yes, you can. Open the value field settings and select the average in the summary method. The mean equals the sum of the group divided by the group count. If you want to compare levels between groups, the mean is more appropriate than the sum.

5. After changing the summary method, the numbers didn't change. What is the reason?

There are two common reasons: first, the pivot table hasn't been refreshed, so source data needs to be refreshed manually after updating; Second, the field you are editing is not the same as the field you want to view. Reopen the value field settings, check the selected fields, and refresh again.

📝 Summary

The value fields in pivot tables are by default summing or counting, and the format of the source data is determined by the format. When you need to make changes, select Sum, Count, or Average in the value field settings. Multiple fields can be processed one by one or dragged in repeatedly. After making corrections, refresh and check the total and group numbers to confirm the results are correct.

Open OmniDoc and process your table file