Basics Updated November 2026

Sum, Count and Average in a pivot table

Click the value header (for example Count of Revenue), choose Value Field Settings, pick Sum, Count, Average, Max, Min or another function and click OK. Excel defaults to Sum for clean number columns and to Count when the column has any text or blanks.

Syntax

Values area > click field > Value Field Settings > Summarize Values By > Sum / Count / Average / Max / Min

Part	What it is
`Sum`	Adds the numbers; default for all-numeric columns
`Count`	Counts non-empty cells; default when the column has text or blanks
`Average`	Arithmetic mean of the non-empty numbers
`Max / Min`	Largest or smallest value in the group
`Count Numbers`	Counts only cells that are numbers

Examples

Rows: Department
Values: Average of Salary

Result: Each department with its mean salary, and the overall mean on the Grand Total row.

The grand total is the average of all rows, not the average of the department averages.

Rows: Team
Values: Max of Hours to Resolve

Result: The slowest ticket per team, one number per row.

Rows: Region
Values: Count of Order ID, Sum of Revenue, Average of Revenue

Result: Three columns per region: how many orders, total revenue, and revenue per order.

Drag Order ID and Revenue into Values; drag Revenue in a second time and change it to Average.

Common mistakes

The pivot shows Count of Revenue when you expected a total: the column holds text or blanks, so change the function to Sum and clean the source.
Average looks too low: blank cells are ignored but zeros are not, so decide whether zeros in the source are real.
You cannot find Value Field Settings: right-click any number in the Values area; it is on that menu too.

Practice Sum, Count and Average in a pivot table

These drills check your answer on live data, free, no account needed.

Count, total and averageFor each Category show the number of claims, the total Amount and the average Amount.
Average salary by departmentShow the average Salary in each Department.
Slowest ticket per teamFor each Team, show the longest time a ticket took to resolve (Hours to resolve).

Questions

Why does my pivot table count instead of sum?

At least one cell in that column is text, a blank or a number stored as text. Excel then treats the whole column as non-numeric and defaults to Count. Fix the source and set the function to Sum.

How do I show sum and average of the same field?

Drag the field into Values twice. Leave the first as Sum and change the second to Average with Value Field Settings.

Can a pivot table calculate a median?

Not in Excel. Google Sheets offers MEDIAN in the Summarize by list; in Excel use a Data Model measure or a helper formula outside the pivot.

Create a pivot tableCreate a pivot table in Excel from a range or table, pick a new worksheet, then drag fields into Rows and Values. Steps, shortcut, data rules and fixes.
Multiple value fieldsAdd two or more fields to Values, decide whether they sit side by side or stacked with the Sigma Values item, and show one field twice as a sum and a count.
Distinct countCount unique customers or products per region. In Excel tick Add this data to the Data Model and pick Distinct Count; in Google Sheets choose COUNTUNIQUE.
Calculated fieldInsert a calculated field with Fields, Items & Sets to compute margin, commission or price per unit from other fields. Formula rules, why averages go wrong.