Excel 2017 Formula - Average data by month, while being filterable

Question

I'm not a VBA coder, and I would prefer an excel formula if possible, the easiest solution will be the best one.

Test workbook screenshot

As you can see, I have plenty of columns, which are filterable.

I am attempting to retrieve an average of Column L, but I want the data to be calculated for the correct month in G3:R3.

The resulting calculation needs to be recalculated when filtered, between customers, sites, status, job type etc.

I am referencing the resulting cells in another sheet, which gives an idea of trends I can glance at, as such filtering by month in each sheet, is not an option.

=AVERAGE(IF(MONTH(E9:E1833)=1,(J9:J1833)))

This one does not update with the filtered data.

=SUM(IF(MONTH(E9:E1833)=1,J9:J1833,0)) /SUM(IF(MONTH(E9:E1833)=1,1))

This one does not update with the filtered data.

I have tried 5 different SUBTOTAL formulas, some with OFFSET, none of these produce the same result I get when checking manually.

Each worksheet has over 1,500 hundred rows, the largest is 29148 rows. The data goes back as far as 2005.

Please can someone help me find a solution?

Answer 1

One possible solution is to create a helper column which returns 1 if the row is visible and returns 0 if the row is invisible (or blank). This allows a bit more freedom in your formulas.

For example, if you want to create a helper column in column X, type this into cell X9 and drag down:

= SUBTOTAL(103,A9)

Now you can create a custom average formula, for example:

= SUMPRODUCT((MONTH(E9:E1833)=1)*(X9:X1833)*(J9:J1833))/
  SUMPRODUCT((MONTH(E9:E1833)=1)*(X9:X1833))

Not exactly pretty but it gets the job done. (Note this is an array formula, so you must press Ctrl+Shift+Enter on your keyboard instead of just Enter after typing this formula.)

With even more helper columns you could avoid SUMPRODUCT altogether and just accomplish this by doing a single AVERAGEIFS.

For example if you type into cell Y9 and drag down:

= MONTH(E9)

Then your formula could be:

= AVERAGEIFS(J9:J1833,X9:X1833,1,Y9:Y1833,1)

There isn't a clean way to do this without at least one helper function (if you want to avoid VBA).