Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
overlapping pivot tables | Excel Discussion (Misc queries) | |||
2 Pivot tables on 1 sheet | Excel Discussion (Misc queries) | |||
#REF in Sheets that refer to Pivot Tables | Excel Discussion (Misc queries) | |||
Three tables on one worksheet, need to hide rows | Excel Discussion (Misc queries) | |||
Problems with updating category names in pivot tables | Excel Discussion (Misc queries) |