CODEWITHSUNDEEP
excelexcel-2010pivot-table
Turtleman10
Turtleman10

Reputation: 119

Grand total for pivot table not giving me the correct amount when using a calculated field

I have a pivot table that I put a calculated field in. the calculation is =IF(fddue_date<finspectData,0,COUNT(fLotSize))

The problem is that instead of getting the expected result of 6 I get 1. Any one have an idea what I am doing wrong?

Row Labels  Count of fLotSize   Sum of Field1
5/14/2014       
5/12/2014   1                   0
5/14/2014   7                   1
5/15/2014   9                   1
5/16/2014   5                   1
5/19/2014   3                   1
5/30/2014   1                   1
6/9/2014    1                   1
Grand Total 27                  1

Upvotes: 2

Views: 40653

Answers (2)

kb211470
kb211470

Reputation: 41

it's by the way a known bug since at least Excel 2003. still in 2013 ...

https://support.microsoft.com/en-us/kb/211470

Upvotes: 3

amoy
amoy

Reputation: 181

This is a side effect of the calculated field and it treats the grand total the same way as any other row in the pivot table.

If you want to use this type of calculated formula and have a sum shown in the grand total, the best way is to create the formula in the source data table and then pull it into your pivot table as a separate column which will show the correct grand total.

Upvotes: 4

Related Questions