Filtering pivot table columns, only count if

Question

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.

Answer 1

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...

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.]

Answer 2

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

• r-click
• filter
• value filters...

Answer 3

1. Left click on any row items drop-down carrot
2. Click 'value filter'
3. Choose value filter ('equals', 'greater than', etc)
4. Choose value field item you'd like it to filter on (eg 'count of days' from the image i can see in the original question)

You can also do it via the right click options as @kztd mentioned