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 CountIf 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. Figure 3.6. Your revenue numbers look anemic. Notice in cell A3 that Excel chose to count instead of sum the revenue. This often happens if you inadvertently have one blank cell in your Revenue column.You can easily override the incorrect Count calculation. Activate the PivotTable Field dialog box by double-clicking on Count of Revenue and then change the Summarize By setting from Count to Sum. Using Functions Other Than Count or SumExcel offers a total of 11 functions in the Summarize By section of the PivotTable Field dialog box. The options available are as follows:
|