filter in pivottable

[stephme55]

Hello. I have a value field that counts occurences. How can I filter this count so it only counts particular text values?

[timiop2011]

Hi. Ideally you would do this quite easily by nesting the SUBTOTAL and COUNTIF functions together, with the idea being the SUBTOTAL function would limit the cells counted for the text determined by the COUNTIF function to visible cells only. However this is not possible in Excel as the SUBTOTAL function can not be combined with criteria based functions (COUNTIF, AVERAGEIF, SUMIF). There is way around this using a more complex method which nests the SUMPRODUCT, SUBTOTAL, AND OFFSET functions. It is excellently explained here: https://exceljet.net/formula/count-v...-with-criteria

[estige]

You can drag and drop the text variable in question to the report filter field, click the arrow next to the variable name above the pivot and choose "Select multiple values".

[stephme55]

@ timiop2011. I'm not sure what you are talking about haha. How does that relate to a pivottable?

@estige. I'll try that out. It's interesting that when you apply that filter it impacts the row instances as well. For example, it only shows the rows that have that value that you filtered on. For example, customers are in rows, type of sales is in values (discount, normal). When I click on filter to normal, it only shows contracts with at least a 1 count for normal, whereas when you have all it shows all contracts even if they didn't have a sale.

Thanks.

[oeldere]

You get better result on your question if you add a small excel file, without confidential information.

Please also add the expected results manualy in your file.