AverageIF within a subtotal

What i want to be able to do is have an AverageIF working within a subtotal.

From the example in the excel sheet, Im doing countIFS within a subtotal in C2:E13 but i need to be able to expand or change the formula to be able to AVERAGEIF in the neighbouring cells, rather than COUNTIF...

I want to be able to dynamically populate the table at the top of the Excel sheet when filtering on other columns on the data dump below (row 18 downwards)

What i need to be able to do in the highlighted yellow area (F2:G13) is average the "Dwell Time" in Column F (or G) IF the cells match the description in column A2:A13.

The reason being that when i filter on Owner in Column D, it will then tell me, by Outlet Type, what the average customer dwell time is in their Bars, or Restaurants etc.

In F2, copied acrss and down:

=SUMPRODUCT(--($A$19:$A$71=$A2),SUBTOTAL(109,OFFSET(F$19:F$71,ROW(F$19:F$71)-ROW(F$19),0,1,1)))/SUMPRODUCT(--($A$19:$A$71=$A2),SUBTOTAL(103,OFFSET(F$19:F$71,ROW(F$19:F$71)-ROW(F$19),0,1,1)))

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.