This is one of the most common complaints encountered when dealing with Pivot Tables<\/a>.<\/p>\n In this article, you will be provided a detailed guide on:<\/p>\n Let’s go through each of these points one-by-one!<\/p>\n <\/p>\n Pivot Table in Excel is one of the most powerful features within Excel that allows you to analyze more than 1 million rows of data with just a few mouse clicks.<\/p>\n Let’s explore the power of analyzing using a Pivot Table with the help of an example.<\/p>\n In the table below, you have sales data containing salesperson name, region, order date and sales amount.<\/p>\n Using this data, you can create a Pivot Table that tells you the number of sales achieved by each salesperson. Follow the steps below to calculate Pivot Table sum.<\/p>\n STEP 1:\u00a0<\/strong><\/span>Select the Data Table<\/strong>.<\/p>\n STEP 2: <\/strong><\/span>Go to Insert<\/strong> > Pivot Table<\/strong><\/p>\n STEP 3: <\/strong><\/span>In the Create PivotTable<\/strong> dialog box, Excel will automatically select the table for you and the default location will be New Worksheet. <\/strong>Press OK.<\/strong><\/p>\n STEP 4: <\/strong><\/span>PivotTable panel will be created in a new worksheet.<\/p>\n STEP 5: <\/strong><\/span>You can drag and drop the Sales Person<\/strong> field under Row<\/strong> area and Sales<\/strong> field under the Values <\/strong>area.<\/p>\n STEP 6: <\/strong><\/span>Below is the Pivot Table that summarizes the total sales amount by each salesperson.<\/p>\n By default<\/strong>, Pivot Table shows the summation<\/strong> of the sales amount. You can easily change the calculation type from Sum to one of the 11 different functions (like count, average, maximum or minimum, etc).<\/p>\n Right-click<\/strong> on the Pivot Table and select Summarize Value By<\/strong> > Count<\/strong>.<\/p>\n The summarization has now changed from Sum to Count Pivot Table.<\/p>\n As you have seen in the previous section when you drag and drop an item in the Value field, it automatically shows the sum of the value.<\/p>\n But if it shows Count instead of Sum. Well, there are three\u00a0reasons<\/strong> why this is the case:<\/p>\n 1. There are blank cells in your values column within your data set; or<\/p>\n 2. There are “text” cells in your values column within your data set; or<\/p>\n 3. A Values<\/em> field is Grouped within your Pivot Table.<\/p>\n So if you have at least one blank cell in a Values<\/em> column, Excel automatically thinks that the whole column is text-based. \u00a0Pretty stupid but that’s the way it thinks.<\/p>\n Also if you have a cell that is formatted as Text\u00a0<\/em>within your Values<\/em> column, then it will also cause it to Count<\/em> rather than Sum<\/em>. \u00a0This usually happens when you download data from your ERP or external system and it throws in numbers that are formatted as text e.g. 382821P<\/p>\n We get the annoying Sales Count in Pivot table below:<\/p>\n <\/p>\n Let’s say that you put\u00a0a Values<\/em> field (e.g. Sales) in the Row\/Column Labels and then you Group it.<\/p>\n When you drop in the same Values<\/em> field in the Values<\/em> area, you will also get a Count of…<\/em><\/p>\n PivotTable basically has a set of rules which is very simple – If all the cells in the field selected contains number, the calculation type will default to SUM.<\/p>\n Now that you know the reason for the error, let’s learn how to fix it!<\/p>\n If you can clean up your data and make sure there are no cells that contain a blank, text, error, or grouped data. This will prevent the default calculation type to turn to Count in Pivot Table instead of Sum.<\/p>\n There are 4 ways to make sure that calculation type is set to Sum:<\/p>\n Sales column in this data table contains blank cells. Let’s use this table to create a pivot table that summarises the total sales amount by each salesperson.<\/p>\n When you drag and drop the Sales column to the Values area, it shows the Sales Count in Pivot Table instead of sum.<\/p>\n To show the summation of the sales instead of Excel Pivot Table count, you can replace the blank cells with zero.<\/p>\n If all the cells in the column field contains numbers, the calculation type will default to SUM.<\/p>\n Follow the steps below to replace blank with zero:<\/p>\n STEP 1:\u00a0<\/strong><\/span>Select the entire column<\/strong> that contains a blank. Press Ctrl + Space Bar<\/strong> to select the entire column.<\/p>\n STEP 2: <\/strong><\/span>Press Ctrl + H<\/strong> to open the Find and Replace <\/strong>dialog box.<\/p>\n STEP 3: <\/strong><\/span>Delete everything in the Find what<\/strong> box and type 0 in Replace with<\/strong> box.<\/p>\n STEP 4: <\/strong><\/span>Press Replace All<\/strong>. This will replace all the blank cells with 0.<\/p>\n Now you can create a Pivot Table as all the values will contain a number!<\/p>\n To replace the error message with zero, you can add an IFERROR formula<\/a><\/strong>.<\/p>\n IFERROR function is used to display custom text<\/strong> when a formula generates an error, and a standard result when no error is detected.<\/p>\n It has two arguments – value<\/strong> and value_if_error<\/strong>.<\/p>\n So if the source column contains an error, you can use the IFERROR formula to return a zero instead of the error.<\/p>\n If a cell contains numbers but is stored as text, you will have to convert it.<\/p>\n To do that click on the small yellow icon<\/strong> on the left, and select Convert to Number<\/strong>.<\/p>\n STEP\u00a01:<\/span><\/strong> Right Click on the Grouped values in the Pivot Table and choose Ungroup<\/strong>:<\/p>\n STEP 2:<\/strong><\/span> Drag the Count of SALES<\/em> out<\/strong> of the Values area and let go to remove it<\/p>\n STEP 3:<\/strong><\/span> Drop in the SALES field<\/strong> in the Values area once again<\/p>\n It\u00a0will now show a Sum of SALES<\/em>!<\/p>\n N.B<\/strong>. Sometimes you will need to locate the Pivot Table that has the Grouped values. \u00a0The SALES field may not be evident that it is Grouped, especially if it is not selected in the Row\/Column labels.<\/p>\n You may need to drag and drop this field from the PivotTable Fields<\/em> and into the Row\/Column Labels area to confirm that it is Grouped.<\/p>\n In this article, you have learned how to summarize value in Pivot Table and how to make the Sum as default calculation type instead of Count when all values in the column contains numbers.<\/p>\n To unveil other powerful features of Excel Pivot Table, Click Here<\/a>.<\/strong><\/em><\/p>\n Further Learning:<\/strong><\/span><\/p>\n\n
<\/a>Summarize value as SUM, COUNT in Pivot Table<\/h3>\n
![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-268.png\" "\\"Sum")
<\/a><\/p>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-275.png\" "\\"Sum")
<\/a><\/p>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-270.png\" "\\"Sum")
<\/a><\/p>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-271.png\" "\\"Sum")
<\/a><\/p>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-272-1.png\" "\\"Sum")
<\/a><\/p>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-273.png\" "\\"Sum")
<\/a><\/p>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-276.png\" "\\"Sum")
<\/a><\/strong><\/h3>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-277.png\" "\\"Sum")
<\/a><\/p>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-278.png\" "\\"Sum")
<\/a><\/p>\n<\/a>Why the Pivot Table values show as Count instead of Sum<\/strong><\/h3>\n
1. BLANK\u00a0CELL(S):<\/span><\/h4>\n
![\"Top](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/blank-cells.jpg\" "\\"Top")
<\/a><\/p>\n2. TEXT\u00a0CELL(S):<\/span><\/h4>\n
![\"Top](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/TEXT-CELL.jpg\" "\\"Top")
<\/a><\/p>\n![\"Top](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/count-of-sales.jpg\" "\\"Top")
<\/a><\/p>\n3. GROUPED VALUES:<\/span><\/h4>\n
![\"Top](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/grouped-sales-1.jpg\" "\\"Top")
<\/a><\/p>\n<\/a>How to fix this issue<\/strong><\/h3>\n
METHOD 1:<\/span> Replace Blank with Zero<\/h4>\n
![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-280.png\" "\\"Sum")
<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-281.png\" "\\"Sum")
<\/a><\/p>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-282.png\" "\\"Sum")
<\/a><\/p>\n![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-283.png\" "\\"Sum")
<\/a><\/p>\nMETHOD 2:<\/span> Replace Error with Zero<\/h4>\n
\n
METHOD 3:<\/span> Convert Text to Numbers<\/h4>\n
![\"Sum](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2018\/03\/Screenshot-285.png\" "\\"Sum")
<\/a><\/p>\nMETHOD 4:<\/span> Ungroup values in the Pivot Table<\/h4>\n
![\"Top](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/ungroup-1.jpg\" "\\"Top")
<\/a><\/p>\n![\"Top](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/04\/grouped-sum-of.jpg\" "\\"Top")
<\/a><\/p>\n<\/a>Conclusion<\/strong><\/h3>\n
![\"Automatically](\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/03\/728x90.gif\" "\\"\\"")
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"