Is there a way to have pivot tables exclude rows in which the DATA amounts
are/total zero? Thanks, Bob
Is there a way to have pivot tables exclude rows in which the DATA amounts
are/total zero? Thanks, Bob
try adding an extra column to the data which contains a label of 'hide' or display' where that value is determined by using a sumif calculation so that if the sum of all trx values for the criteria column is zero then it gets a label of 'hide' else 'display'. Then in the pivot table put the 'hide/display' label in the Page area and select only 'Display'.
Formula would look like assuming the column headings were in row A, the values in B and data in rows 2 to 100...
'=IF(SUMIF($A$2:$A$100 [the criteria column],$A$2:$A$100 [the criteria column],$B$2:$B$100 [values of the criteria items]) =0,"Hide","Display")
... it worked when I tested it :-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks