Bottom line: Learn how to get the pivot table to default to a Sum calculation instead of Count when the source data field contains numbers. Show
Skill level: Beginner Video: How to Default the Pivot Table Calculation Type to SumWatch in full screen HD or on Youtube. Why does the Pivot Table Default to Count?One of the most common questions I see on my free 3-part video series on pivot tables & dashboards is, “Why does the pivot table default to Count instead of Sum when I add a field to the values area?”  Well, Pivot Tables have some rules on which calculation type will be selected when we add a field to the Values area. The rules are pretty simple:
Now that we know the rules, we can prevent this error by cleaning up our data. How to Prevent the Default Count Calculation TypeLet's take a look at 3 ways to make sure the calculation type defaults to Sum. 1. Replace Blank Cells with ZerosIn the video above I show a data set that contains blanks in the Revenue column. Since there are blanks in this column, the pivot table calculation type defaults to Count. One quick way to fix this is to replace the blank cells with a zero (0) value. Here are instructions on how to find & replace all blanks in a column.
The calculation type should default to a Sum calculation if all cells in the data source column are numbers. 2. Replace Errors with ZerosBlank cells are the most common cause that I see for calculation type defaulting to Count. However, cells that contain text or errors can also cause this problem. If the source data column contains a formula that is returning errors, we can use an IFERROR function to return a zero instead of the error. Of course we will want to determine why the error is occurring in the first place. My free 3-part video series on the lookup formulas has an entire video dedicated to learning how to prevent and fix errors with formulas like VLOOKUP and INDEX/MATCH. 3. Convert Text to NumbersSometimes values in cells look like numbers, but the numbers are stored as text. This is a common issue when you are importing data into Excel from another system. In this case we will need to convert the text to numbers first. Checkout my article on keyboard shortcuts to convert text to numbers to learn more about these techniques. PivotPal's Auto Sum/Count FeatureAt the end of the video I also explain how PivotPal can save time with this issue. PivotPal has it's own rules for adding a field to the values area, and does NOT default to count if the column contains blanks. If the column contains numbers and blanks, PivotPal will default the calculation type to Sum when we add the field to the values area using the PivotPal window. This is one way to get around this annoying issue without having to modify the source data. The PivotPal Add-in is packed with features that will save you time when working with pivot tables in Excel. Please click the link below to learn more and give PivotPal a try today. Click here to learn more about The PivotPal Add-in Free Webinar on Pivot TablesRight now my good friend John Michaloudis from My Excel Online has a pivot table webinar going on. This is a free hour long training on how to get started with pivot tables in Excel. Here are some of the Excel skills you will learn:
Click the link below to register for the free webinar and pick a time that works for you. Click here to register for the free webinar on pivot tables What Other Issues Cause the Calculation to Default to Count?Is your calculation type still defaulting to Count? This is typically due to one or more cells that are NOT numbers. Please leave a comment below with any other issues that I might have missed, or any questions you have. Thank you! 🙂 Why is my sum not working in pivot table?Reason No.
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.
Can you use sum in calculated field?Sum is the only function available for a calculated field. A calculated field becomes a new field in the pivot table, and its calculation can use the sum of other fields. Calculated fields appear with the other value fields in the pivot table.
How do you summarize text cells in Excel?Select the column to summarize on
With a cell selected in an Add-In for Excel table, click the ACL Add-In tab and select Summarize > Summarize. Select a column of any data type to summarize on.
How do I enable a calculated field?On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field. In the Name box, type a name for the field. In the Formula box, enter the formula for the field. To use the data from another field in the formula, click the field in the Fields box, and then click Insert Field.
|
