Changing Summary Calculations

When creating your pivot table report, the PivotTable Wizard will, by default, summarize your data by either counting or summing the items. Instead of Sum or Count, you might want to choose functions such as Min, Max, and Count Numeric. In all, 11 options are available. However, the common reason to change a summary calculation is because Excel incorrectly chose to count instead of sum your data.

One Blank Cell Causes a Count

If all the cells in a column contain numeric data, Excel will choose to sum. If just one cell is either blank or contains text, Excel will choose to count. Be vigilant while dropping fields into the data section of the pivot table. If a calculation appears to be dramatically too low, check to see if the field name reads “Count of Revenue” instead of “Sum of Revenue.” When you created the pivot table in Figure 3.6, you should have noticed that your company only had $41,549 in revenue instead of $800 million. This should be your first clue to notice that the heading in A3 reads “Count of Revenue” instead of “Sum of Revenue.” In fact, 41,549 is the number of records in the dataset.