XL: Pivot Table Uses COUNT Instead of SUM with Blank Cells
ID: Q110599
|
The information in this article applies to:
-
Microsoft Excel for Windows, versions 5.0, 5.0c
-
Microsoft Excel for Windows NT, version 5.0
-
Microsoft Excel for Windows 95, version 7.0
-
Microsoft Excel 97 for Windows
SUMMARY
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:
- In the PivotTable Wizard Step 3 of 4 in the Data field, double-click the
field button.
- In the Summarize By List box, click Sum.
- Click OK.
Steps to Reproduce Behavior
- In Microsoft Excel, create a new workbook.
- 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:
- Select any cell in the range A1:C5. On the Data menu, click
PivotTable or PivotTable Report.
- Select Next in Step 1 of 4 of the PivotTable Wizard. In Step 2 of 4
select Next to accept the range A1:C5.
- 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.
- 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.
- Click Finish in Step 3 of 4 to create your PivotTable.
REFERENCES
"User's Guide," version 5.0, Chapter 24
For more information about Pivot Tables, click the Search button in
Help and type:
Pivot
Additional query words:
8.00 97 pt XL97
Keywords : xlpivot
Version : WINDOWS:97,7.0,5.0,5.0c
Platform : WINDOWS
Issue type :
|