Pivot Tables - Exclude Rows With Zero Balances
 

Is there a way to have pivot tables exclude rows in which the DATA amounts
are/total zero? Thanks, Bob

Pivot Tables - Exclude Rows With Zero Balances
 

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 :-)


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=573921

Pivot Tables - Exclude Rows With Zero Balances
 

Works great... thanks!

"steven1001" wrote:


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 :-)


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=573921