Calculate monthly average in pivot table with distinct count

[CEdward]

Greetings,

I am trying to calculate the average across the total number of months per year wherein the month has either passed or is current, by another variable.

This post https://www.excelforum.com/excel-cha...vot-table.html actually provides the solution to what I am looking for, but I am tried amending it to include a distinct count of the number of months that satisfy what I just stated. The implication is that the denominator is not biased upwards unnecessarily making the average smaller than it should be.

I followed the steps exactly as the solution posted, but I still run into an error.

1. I added a field, call it Sum_Expenses, which has the following formula: =Sum(Expenses_Amount) where Expenses_Amount contains all the expenses I am summing.

2. I tried to add another field that averages across months, call it Average_Expenses, which has this formula: =[Sum_Expenses]/SUM(IF(ISTEXT('Expense Data'!E2),1/COUNTIF('Expense Data'!E2:E9999,'Expense Data'E2:E9999),"")) where the denominator counts the number of distinct months (text variable) in a year. The numerator and denominator each work fine independently, but when I try to add this field, I get this error (see image attached).

Note that I used E2:E9999 because for some reason $E:$E doesn't work. Presumably I can run a slicer on the year and the average will adjust accordingly.