The #1 complaint that I get from Pivot Tables is “Why do my values show as a Count of rather than a Sumof ?”

Well there are three reasons why this is the case:

1. There are blank cells in your values column within your data set; or

2. There are “text” cells in your values column within your data set; or

3. A Values field is Grouped within your Pivot Table.

 

1. BLANK CELL(S):

So if you have at least one blank cell in a Values column, Excel automatically thinks that the whole column is text based.  Pretty stupid but that’s the way it thinks.

Count VS Sum in Pivot Tables

   

2. TEXT CELL(S):

Also if you have a cell that is formatted as Text within your Values column, then it will also cause it to Count rather than Sum.  This 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

Count VS Sum in Pivot Tables

We get the annoying Count of Sales below:

Count VS Sum in Pivot Tables

Have a look at the following tutorials that show you how to locate blank cells: Find Blank Cells In Excel With A Color

EXCEL FIX:

STEP 1: You will need to enter a value or a zero within this blank or text formatted cell(s)

STEP 2: Go over to your Pivot Table, click on the Count of….  and drag it out of the Values area

STEP 3: Refresh your Pivot Table

STEP 4: Drop in the Values field (SALES) in the Values area once again

Count VS Sum in Pivot Tables

 

    3. GROUPED VALUES:

Let’s say that you put a Values field (e.g. Sales) in the Row/Column Labels and then you Group it.

When you drop in the same Values field in the Values area, you will also get a Count of…

Count VS Sum in Pivot Tables

EXCEL FIX:

STEP 1: Right Click on the Grouped values in the Pivot Table and choose Ungroup:

Count VS Sum in Pivot Tables

STEP 2: Drag the Count of SALES out of the Values area and let go to remove it

STEP 3: Drop in the SALES field in the Values area once again

It will now show a Sum of SALES!

Count VS Sum in Pivot Tables

N.B. Sometimes you will need to locate the Pivot Table that has the Grouped values.  The SALES field may not be evident that it is Grouped, especially if it is not selected in the Row/Column labels.

You may need to drag and drop this field from the PivotTable Fields and into the Row/Column Labels area to confirm that it is Grouped.

 

Helpful Resource:

Automatically Refresh a Pivot Table

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Show The Percent of Parent Row Total With Excel Pi... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT ROW TOTAL calculation.This is a new calculation in Excel 2010 and onwards.This option will immediately calculate the percentages f...
Sort an Item Row (Left to Right) With Excel Pivot ... I knew that I could sort virtually anywhere with Excel Pivot Tables, but I was surprised that I could even sort any row from left to right!!Below I have an Excel Pivot Table that consists of Sales Numbers over a three year period.Make sure to download the Excel Workbook b...
The Ultimate Guide to Excel Pivot Table Slicers Slicers were first introduced in Pivot Tables in Excel 2010 for Windows and Excel 2016 for Mac.Pivot Table Slicers are a visual filter in the form of an interactive button.  There are several cool things that you can do with Pivot Table Slicers, like customize them, filter th...
Use An External Data Source To Import Data Into An... When creating an Excel Pivot Table, what happens if your data source is in another location?Would you have to copy your data into the same spreadsheet?Well, NO!  You can simply use the External Data Sources feature in your Pivot Table and Excel will magically import the d...