Spreadsheets: Why Pivot Tables Won’t Sum

Is there any way in Excel 2007 to have a pivot table always default to sum the amounts rather than count them?


Editor’s Note:

To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.

Eric S. wins an autographed copy of Pivot Table Data Crunching from CFO and MrExcel for his question: “Is there any way in Excel 2007 to have a pivot table always default to sum the amounts rather than count them?”

A pivot table created from a well-formed dataset will automatically sum the numeric fields. When you have a pivot table that counts instead of sums, it is caused by one of three reasons.

Reason No. 1: There Are One or More Blank Cells in the Column

Excel expects your numeric data to be 100% numeric. If you have a dataset with 50,000 rows of numbers and one blank cell in the middle, the pivot table will count instead of sum. There is an easy way to convert the blanks to zero. Follow these steps:

  1. Select the entire range that should contain numeric data.
  2. Press Ctrl G to display the Go To dialog.
  3. In the lower-left corner of the Go To dialog, click Special to display the Go To Special dialog.
  4. Choose Blanks from the dialog and click OK. Only the blank cells will be selected.
  5. Type a zero and press Ctrl Enter. This will fill the blank cells with zero.

Be careful after Step 4: if you get the message “No Cells Were Found,” then there are no blank cells in the range. Do not proceed to Step 5.

Reason No. 2: There Are One or More Cells in the Column that Contain Text, an Error, or True/False

Just as with the blank cell, having any cell contain #N/A! or DIV/0!, True, False, or even a number stored as text will cause the pivot table to count instead of sum. As with Reason No. 1, you can use the Go To Special dialog to find the offending cells. Follow these steps:

  1. Select the entire range that should contain numeric data.
  2. Press Ctrl G to display the Go To dialog.
  3. In the lower-left corner of the Go To dialog, click Special to display the Go To Special dialog.
  4. If the range contains formulas, choose Formulas from the dialog and uncheck Numbers as shown in Figure 1 (below, left). This will specify that you are looking for Formulas that result in Text, Logicals, or Errors. Click OK. Use the Tab key to move to all of the cells that are causing problems. Fix each cell.

Fig. 1

MrExcel11-Fig1

Discuss

Your email address will not be published. Required fields are marked *