Excel like a finance pro.
Pivot tables · Calculations
Changing the summary function
Switches a value field between Sum, Count, Average, Max, Min, and others.
When to use it
A value field can be summarized as Sum, Count, Average, Max, Min, Product, and several statistics. Sum is the default for numeric columns and Count for anything else.
The shape of it
- How
Click the value field in the Values area, Value Field Settings, choose the function.
Worked examples
Average
Click the value field in Values, Value Field Settings, Average, OK → Average sales per row instead of the total
Averages by group.
Count
Value Field Settings, Count → How many records per group
Order counts.
Max
Value Field Settings, Max → The largest sale per group
Extremes.
Worth knowing
- A field that lands as Count instead of Sum usually has text or blanks in the source column.
- A field that lands as Count instead of Sum usually has text or blanks in the source column.
- Right-click a value cell, Summarize Values By, is the fast route.
- Add the same field twice for Sum and Count side by side.
Where it goes wrong
- Average of a pivot is the average of rows, not the average of averages.
- Count counts non-blank cells; blanks are skipped.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.