Problem with pivot table sums - Blank cells are not carried through in totals

[#1]

I have the problem illustrated in the screenshot below.  In the red ovals, any time there's a blank cell, the TOTAL column and rows do not get a value.  I suspected that it was because my field was a NULL value, so I did a SUMIF.  It did not work.

=NUM(SUM(IF(ISNULL(CEM_EAD),0,CEM_EAD)),MoneyFormat)

[stevedark]

Hi there,

You shouldn't need the IF statement at all, but the syntax if you do want to replace a null with a value (or zero) is Alt(CEM_EAD, 0).  Also, I would avoid putting the NUM function in your expression, strip this out and use the format options on the Number tab of the properties.  Hopefully this will resolve the issue.

If it does not it will be worth working out if there are values in those cells (perhaps spaces?) or whether there are simply no rows.  You can do this by selecting the NETG_FLAG of Y.  If BNK is still shown on the left then there is a rogue value in there, if not then there are no rows that share the values BNK and Y.

If there are values that need cleaning then I would suggest doing this in the load script.

Hope that helps,

Steve