Home > Pivot > Calculation > Summary Functions Pivot Table Summary Functions In an Excel pivot table, Values are shown as Sum or Count. Learn why this happens, and see how to change to other functions. Get the free workbook below.

Introduction

When you add a field to the pivot table's Values area, 11 different functions, such as Sum, Count and Average, are available to summarize the data.

The summary functions in a pivot table are similar to the worksheet functions with the same names, with a few differences as noted in the descriptions that follow.

Change the Summary Function

When you add a numerical field to the pivot table's Values area, Sum or Count will be the default summary function. The default function can't be changed -- it is applied based on the field's contents:

• If the field contains numbers, Sum will be the default
• If the field contains text or blank cells, Count will be the default

After a field has been added to the pivot table, to select a different summary function, follow these steps:

1. Right-click on a cell in the Value field that you want to change.
2. In the pop-up menu, click Summarize Values By
3. Click on the Summary Function that you want to use

Totals and Subtotals

The selected summary function will automatically be used in the subtotals and grand totals for that field. You can select a different function for the totals -- see the instructions here.

However, the totals calculated on the source data, not on the values showing in the pivot table. For example, if a field uses the MAX summary function, and the subtotal shows the AVERAGE, it will be an average from the values in the source data, not an average of the MAX values. (To calculate the Average of the Max values, you could use formulas outside of the pivot table, or create a new pivot table, based on the original one.)

Change Multiple Fields at Once

To change many fields at once, you can use a macro.

Sum Function 

The pivot table's Sum function totals all the underlying values for each item in the field. The result is the same as using the SUM function on the worksheet to total the values. Blank cells, and cells with text are ignored.

When you add a numerical field to the pivot table's Values area, Sum will be the default summary function. (Note: If the field contains text or blank cells, Count will be the default.)

In the screen shot below, you can see the source data for a small pivot table, and the total quantity, using the worksheet's SUM function, is 317.

With a pivot table, you can quickly see the total sum for each product that was sold, and the grand total -- 317 -- which matches the worksheet total.

Sum Function Macro

Instead of manually changing each data field to the Sum function, you can run a macro that will change the summary function in each data field.

You can copy this code to a regular code module in your workbook, and run it when you want to change the summary functions for all the Value fields.

Sub SumAllValueFields()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim ws As Worksheet

  Set ws = ActiveSheet
  Set pt = ws.PivotTables(1)
  Application.ScreenUpdating = False

    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = xlSum
    Next pf
    pt.ManualUpdate = False

  Application.ScreenUpdating = True
  Set pf = Nothing
  Set pt = Nothing
  Set ws = Nothing
End Sub

Count Function

Count is the default summary function when fields with nonnumeric or blank cells are added to the Values area. The Count function's name is slightly confusing, because it's like the COUNTA worksheet function, not the COUNT worksheet function.

The pivot table Count function counts:

• text
• numbers
• errors

Blank cells are NOT counted.

Count Blank Cells

In a pivot table, the Count function does not count blank cells. So, if you need to show counts that include all records, choose a field that has data in every row.

This short video shows two examples, and there are written steps below the video.

Blank Cells in Data

In the product sales data shown below, cell C7, in the Qty column, is blank.

• There are 9 entries in the Product column, with 2 orders for Pens
• There are 8 entries in the Qty column, with 1 number for Pens

Count Product Orders

Using that product sales data, we want to create a pivot table that shows the number of orders for each product.

To get the pivot table started, follow these steps:

1. Select a cell in the orders table
2. On the Excel Ribbon's Insert tab, click Pivot Table
   • The table name should automatically appear in the Table/Range box
   • For the location, choose New Worksheet
   • Leave the Data Model box unchecked
   • Click OK
3. In the PivotTable Fields list, check the Product field, to add it to the Rows area

Usually, we put numeric fields into the Values area of a pivot table.

To see what happens with this data, follow these steps:

1. In the PivotTable Fields list, check the Qty field, to add it to the Values area
2. Qty appears in the pivot table as Sum of Qty
3. Right-click a cell in the Sum of Qty column
4. Point to Summarize Values By, then click Count

Because one of the Qty cells is blank, it is not counted.

• There are 2 orders for Pens, but the count of Qty is 1.

Fix the Problem

To get the count of all orders, even if the Qty cells are blank, follow these steps:

1. In the PivotTable Fields list, uncheck the Qty field, to remove it from the Values area
2. Drag another copy of the Product field into the pivot table, and place it in the Values area
3. Because Product is a text field, it will automatically summarize as Count.

Because none of the Product cells are blank, the count includes all the orders.

• There are 2 orders for Pens, and the count of Product is 2

TIP: Rename the Count of Product column as "Orders"

Average Function

The Average function totals all the underlying values in the Values area, and it divides by the number of values. The result is the same as using the AVERAGE function on the worksheet to calculate the average (mean) of the values.

Blanks and Zeros

Blank cells, and cells with text, are ignored when calculating the pivot table averages, but zero cells are included.

In the data source shown below, cell C7 is blank, and is not included in either the worksheet average (C12), or the pivot table average, shown below.

If you have formatted the worksheet to hide zero values, remember that those zero values will be included in the averages, even if the cells appear blank.

Format the Results

When you use the Average summary function, the results will probably show a strange mixture of decimal places, as shown in the pivot table at the left, in the screen shot below.

Format the field to have a consistent number of decimal places (as in the pivot table at the right, below), so the numbers are easy to compare.

Max Function

The Max summary function shows the maximum value from the underlying values in the Values area. The result is the same as using the MAX function on the worksheet to calculate the maximum of the values.

In the screen shot below, you can see the source data for a small pivot table, and the maximum quantity, using the worksheet's MAX function, is 97.

With a pivot table, you can quickly see the maximum for each product that was sold, and the grand total -- 97 -- which matches the worksheet maximum.

Min Function

The Min summary function shows the minimum value from the underlying values in the Values area. The result is the same as using the MIN function on the worksheet to calculate the minimum of the values.

In the screen shot below, you can see the source data for a small pivot table, and the minimum quantity, using the worksheet's MIN function, is 8.

With a pivot table, you can quickly see the minimum for each product that was sold, and the grand total -- 8 -- which matches the worksheet minimum.

In both the worksheet and the pivot table, the blank cell is ignored when calculating the minimum amount.

Product Function

The Product summary function shows the result of multiplying all the underlying values in the Values area. The result is the same as using the PRODUCT function on the worksheet to calculate the product of the values.

I've never had to use the Product summary function in a pivot table, and can't imagine a situation where it would be useful. However, you might have a use for it, and here's how it works.

In the screen shot below, you can see the pivot table source data, with the PRODUCT calculated for each product group. At the bottom of the source data is the overall PRODUCT calculation.

The results of the Product function may be very large numbers and default to a Scientific number format. You can format the numbers as Number format, instead of Scientific format.

Note: Excel only stores and calculates with 15 significant digits of precision, so after the 15th character you'll only see zeros.

Count Numbers Function

The Count Numbers summary function counts all the underlying numbers in the Values area. The result is the same as using the COUNT function on the worksheet. Blank cells, errors, and text are not counted.

In the screen shot below, you can see the source data for a small pivot table, and the count of the numbers in the Qty column (column C). In cell C4, the value of 20 is entered as text, so that cell isn't counted.

Count Numbers vs. Count

In the pivot table shown below, the Qty field has been added twice to the Values area. In column B, the summary function is Count Numbers, and the Grand Total is 7.

In column C, the summary function is Count, which includes text, so the Grand Total for that column is 8.

StdDev Function and StdDevP Function

Like the STDEV.P and STDEV.S worksheet functions, the StdDevp and StdDev summary functions calculate the standard deviation for the underlying data in the Values area. The standard deviation is a measure of how widely the values vary from the average of the values.

The StdDevP summary function should be used when the entire population is used in the calculation. When a sample of the data is used, not the entire population, then use the StdDev summary function.

In the screen shot below, you can see example pivot table source data, and the STDEV.P worksheet function is calculating the standard deviation for each product type. For the File Folders, there is a large difference between the quantities sold, and the standard deviation is high -- 44.5. For Paper, the difference in quantity is much smaller, and the standard deviation is low -- 4.7.

When the Qty field is added to the pivot table, change the summary calculation to StdDevp.

In the screen shot below, you can see that the standard deviations in the pivot table are the same as those that were calculated on the worksheet.

Note: If the count of items is one, a #DIV/0! error is displayed when using the StdDev summary function, because one is subtracted from the count when calculating the standard deviation.

How the Standard Deviation is calculated

For the standard deviation, each number is compared to the mean of the numbers. You could calculate the standard deviation on the worksheet, without using the STDEV.P function.

1. Find the average of the numbers in the pivot table data.
2. From each number, subtract the average.
3. Square the calculated difference for each number
4. Find the average of the squared difference.
5. Find the square root of the average.

Var Function and Varp Function

The Var and Varp summary functions work like the VAR.P and VAR.S worksheet functions, to calculate the variance for the underlying data in the Values area, and variance is a measure of how widely the values vary from the average of the values.

When the entire population is used in the calculation, the VarP summary function is used. For a sample of the data, instead of the entire population, use the Var summary function.

In the screen shot below is the example pivot table source data, with the VAR.P worksheet function calculating the variance for each product type. For the File Folders, where there is a wide difference between the two quantities, the variance is large -- 1980.25. For the paper sales, there is a small difference in quantity, and the variance is only 22.22.

To show the variance, when the Qty field is added to the pivot table, change the summary calculation to Varp.

As you can see, the variances shown in the pivot table are the same as those that were calculated on the worksheet.

Note: If the count of items is one, a #DIV/0! error is displayed when using the Var summary function, because one is subtracted from the count when calculating the variance.

How the Variance is calculated

For the variance, each number is compared to the mean of the numbers. You could calculate the variance on the worksheet, without the VAR.P function.

1. Find the average of the numbers in the pivot table data.
2. From each number, subtract the average.
3. Square the calculated difference for each number
4. Find the average of the squared difference.

Distinct Count

In a pivot table you might want to see a count of unique (distinct) items in a segment, instead of an overall count. For example, if pens and binders are sold in different colours, how many colours were there for each product?

There isn't a built-in "Unique Count" feature in a normal pivot table, but a Distinct Count calculation is available for OLAP-based pivot tables only, such as Power Pivot, or pivot tables based on the Data Model. For other ways to get a distinct count (unique count), see the Count Unique Items page.

This video shows how to get a distinct count with Power Pivot, and sample file is in the download section.

NOTE: There are written steps for the Data Model, below the video.

Sample Data

In this example, the pivot table's source data is a table with information about office supply sales. There are 3 fields in the table - Product, Colour and Quantity. You can download the Summary Function OLAP sample file to see the data and pivot table.

Pivot Table - Data Model

I created a pivot table from the office supplies source data, and added a check mark to the option, "Add this data to the Data Model".

This creates an OLAP-based pivot table, instead of a normal pivot table.

List the Colours

In the pivot table, Product and Colour are in the Rows area, and Colour is also in the Values area, as Count of Colour. This shows an overall count -- how many items of each colour are in the source data.

In the Rows area, there are 5 unique colours listed under Binders, and 3 unique colours listed under Pens, but the pivot table doesn't give us those unique counts.

Show the Distinct Count

To get a unique count of colours for each product, follow these steps:

• Right-click one of the values in the Count of Colours field
• Click Value Field Settings

• In the Summarize Value Field By list, scroll to the bottom, and click Distinct Count, then click OK

The Colour value field changes, and the Product subtotals show a distinct count of the colours sold for each product type.

If you don't need to see the colour names,

• collapse the Product field, to hide the colour names (shown below)
• OR, remove the Colour field from the Rows area

Errors in Source Data

If there are error values in the source data, the pivot table will display an error for that data, except as noted below, for Count and Count Nums.

In this example, the Total field contains a #VALUE! error and a #DIV/0 error, one blank cell (E7), and one cell with text (E9).

Errors with Count and Count Numbers

These two summary functions count the errors, or ignore them. The errors are not shown in the item totals.

• Count Numbers: Blank cells, errors, and text are not counted.
• Count: Text, numbers and errors are counted. Blank cells are not counted.

Errors with Other Summary Functions

For all other Summary Functions, if errors are in the source data field:

• the first error encountered in the source data is displayed in the pivot table
• the total is not calculated - it shows the first error from the source data.

In the data, #VALUE! is the first error listed, so it appears in the pivot table.

However, if you sort the data with the latest dates at the top, the #DIV/0! error is first. Then, refresh the pivot table, and it shows the #DIV/0! error.

Totals and Subtotals

If subtotals, or row and column totals, are displayed, affected totals and subtotals display the error.

And even though they don't show errors in the item totals, the Count and Count Numbers functions will also display errors in their totals, if both of these conditions are met:

• other summary functions are included in the pivot table, and those fields contain errors in the data
• the Count and Count Number fields contain errors in the data

For example, in the screen shot below, an Average for the Price field has been added, and that field contains a #DIV/0! error. As a result:

• The Count Nums and Count Grand Totals show the #VALUE! error, because they're based on the Total field, which contains errors in the data
• However, the "Count of Date" Grand Total is correct, because the Date field does not contain any errors in the data

Get the Workbooks

1. Summary Functions: To test the summary functions, you can download the Summary Functions sample file. It contains the stationery supplies sample data, and sample data with errors.
2. Count Blanks: To follow along with the Count Blank Cells example above, and the Count Blank Entries example from the Pivot Tables blog, download the Count Blanks in Pivot Table sample file. The workbook is in xlsx format, and does not contain any macros.
3. Distinct Count: The Summary Function OLAP sample file has a pivot table with its source data added to the Data Model., and is OLAP-based, so the Distinct Count calculation is available.
4. Distinct Count Power Pivot Video: To follow along with the Distinct Count with Power Pivot video, download the Power Pivot sample file.
5. Macros: To test the Sum All macro from this page, and a couple of other Summary Function macros, download the Summary Functions Macros file. It contains macros, and buttons to run those macros.

More Tutorials

Value Group Slicers

Excel Error Values

Pivot Table Count Blanks

Pivot Table Time Values

Grouping Data

Running Totals

Clear Old Items in Pivot Table