2

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
Turtleman10
  • 119
  • 1
  • 2
  • 14
  • Put here part of your source data - header row and a few rows as a sample. Put here also a screenshot of your pivot table setttings and the pivot table itself. This will help. – Honza Zidek May 15 '14 at 17:45
  • I do not have enough reputation to post an image. I don't think its a feature because I get a 1 in the grand total I would expect it to be nothing if that was the case. – Turtleman10 May 15 '14 at 19:23

2 Answers2

4

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.

amoy
  • 181
  • 3
3

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

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

kb211470
  • 41
  • 1
  • 3
    Still present in Excel 2016. Such a shame that something as basic as a simple percentage can't be shown in a pivot table, and after almost 15 years Microsoft hasn't fixed it. – jesjimher Aug 24 '17 at 08:36
  • I don't think this is a bug, Google Sheets has the exact same behavior. – Alex Angelico Nov 16 '20 at 22:15