STEP 3: Drop in the SALES field in the Values area once again STEP 2: Drag the Count of SALES out of the Values area and let go to remove it STEP 1: Right Click on the Grouped values in the Pivot Table and choose Ungroup: When you drop in the same Values field in the Values area, you will also get a Count of… Sales) in the Row/Column Labels and then you Group it. Let’s say that you put a Values field (e.g. STEP 4: Drop in the Values field (SALES) in the Values area once again STEP 2:Go over to your Pivot Table, click on the Count of…. STEP 1: You will need to enter a value or a zero within this blank or text formatted cell(s) Have a look at the following tutorials that show you how to locate blank cells. Pivot Table not showing correct data and you will get the annoying Count of Sales below:

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. Pretty stupid but that’s the way it thinks.Īlso 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. So if you have at least one blank cell in a Values column, Excel automatically thinks that the whole column is text-based. A Values field is Grouped within your Pivot Table. There are blank cells in your values column within your data set orĢ.There are “text” cells in your values column within your data set orģ. Well, there are three reasons why Pivot Table not counting correctly:ġ. The no1 complaint that I get is “Why do my values show as a Count of rather than a Sumof ?” Want to know how to create Excel Dashboards?

Cant select cell office mac 2017