XL: Pivot Table Uses COUNT Instead of SUM with Blank Cells

Article translations Article translations
Article ID: 110599 - View products that this article applies to.
This article was previously published under Q110599
Expand all | Collapse all

On This Page


Microsoft Excel automatically uses the SUM function for numeric data and the COUNT function for non-numeric data in the Data area of a pivot table. If the numeric field contains blank cells, the PivotTable Wizard will default to COUNT instead of SUM. If all value cells contain numbers, the default will be SUM as expected.

If you want to change the function that is used in the data field, do the following:
  1. In the PivotTable Wizard Step 3 of 4 in the Data field, double-click the field button.
  2. In the Summarize By List box, click Sum.
  3. Click OK.

Steps to Reproduce Behavior

  1. In Microsoft Excel, create a new workbook.
  2. Enter the following values into a worksheet in the workbook:
          A1: Region   B1: Salesperson  C1: Sales
          A2: a        B2: Joe          C2: 1
          A3: b        B3: Cindy        C3:
          A4: c        B4: Tom          C4: 1
          A5: d        B5: Steve        C5:
  3. Select any cell in the range A1:C5. On the Data menu, click PivotTable or PivotTable Report.
  4. Select Next in Step 1 of 4 of the PivotTable Wizard. In Step 2 of 4 select Next to accept the range A1:C5.
  5. Drag the Region field button to the row area, drag the Salesperson field button to the column area, and drag the Sales field button to the data area. Notice the Sales field button changes to Count of Sales.
  6. Double-click the Count of Sales field button in the data area. Click Summarize by Sum and click Ok. The caption changes to Sum of Sales.
  7. Click Finish in Step 3 of 4 to create your PivotTable.


"User's Guide," version 5.0, Chapter 24

For more information about Pivot Tables, click the Search button in Help and type:


Article ID: 110599 - Last Review: August 15, 2003 - Revision: 1.1
  • Microsoft Excel 97 Standard Edition
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com