Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Filtering pivot table columns, only count if

I was wondering if it's possible to filter a single pivot table column in excel. In other words, when you summarize the data by count, that the data for a certain column is only counted if it has a certain value.

I know that it's possible to get these values by report filtering, but then I need a separate pivot table for the values I don't want to be filtered. Also, some values are 0 and thus are not displayed if report-filtered. Changing the option to display 0 values doesn't change anything. Thanks in advance.

like image 730
andrew b Avatar asked Aug 31 '12 06:08

andrew b


2 Answers

Yes, you can add a filter to a pivot report by selecting a cell that borders the table (but is outside the pivot area) and choosing Filter from the Data tab.

To add a filter to just the Count Of column select the cell above and the cell containing the title and then choose the Filter option from the menus as shown...

enter image description here

To only count data if it fulfills a certain condition, I think you would need to add another column called OK to the source data, with a formula like IF(Status="OK",1,0). Then add this to the pivot table layout. [Note: there is a calculated field option for pivot tables but it only seems to work as you would expect if data is displayed - in this case status would need to be showing.]

like image 78
lori_m Avatar answered Oct 05 '22 15:10

lori_m


That's not working on my Excel 365. Try selecting the 1st field on the first row then:

  • r-click
  • filter
  • value filters...
like image 32
kztd Avatar answered Oct 05 '22 15:10

kztd