Find the number of checked elements of a pivot table filter

Question

I have a pivot table whose data can be filtered by selecting multiple months. It's last column is a grand total figure, and the column to the right of that is supposed to be a monthly average (grand total / # of months). Right now the formula for average is =IF(E34="","",E34/3), i.e. it is hardcoded for the average of 3 months. However if more months are selected in the pivot table month filter, this will be incorrect. Is there a way to find the number of selected items in a pivot table filter?

Thanks.

Answer 1

Yes, a few ways actually. To officially answer your question, you could do a counta formula, which looks for cells that are not empty. Not a good idea here because you'll have a header and grand total, but it can be done =COUNTA(F4:F17)-2 would take the number of cells not empty, minus 2 for the header and total row to return 12 if it were referencing a pivot table with all 12 months filtered in.

My advice would be to do this:

1. add the field you want an average of to the Values section of the field list (you can select the same item more than once in pivot tables)
2. left click on it (it'll be in the bottom right) and choose "Value Field Settings"
3. under Summarize value field by, select Average (by default, either count or sum will most likely be selected)
4. Now you have a new column with a monthly average
5. if you do not need the sum, you can simply edit the existing column

Good luck!