SubTotal & SubProduct with Filter problem?

[ScorpioCRO]

Hello,

Well i got one problem. I am trying to calculate some average times with conditions.
TL;DR version ..

In "L" row is my condition that i want to check and in "M" row is the time that i need to get AVERAGE.

So to check with filter results i am using formula:
=SUBTOTAL(101;M7:M22800)

But since I am "lazy" i would like to get all 3 results once i selected date in filter. So to get count i use formula:
=SUMPRODUCT((L7:L22800="Car")*(SUBTOTAL(3;OFFSET(L7;ROW(L7:L22800)-MIN(ROW(L7:L22800));0))))

But now i want to get average time that (example from above) car is used. I use formula:
=SUMPRODUCT(SUBTOTAL(101;OFFSET(M7:M22800;ROW(M7:M22800)-ROW(M7:M22800););--(L7:L22800="Car"))

I think formula is giving me AVG for entire sheet, not only for filter results (tbo i cant figure out what number do i get)

So any help about it? I tried searching online but can't find anything usefull or they all say me to use above formula that is not working after i filter results.

 

[Marcelo Branco]

To get the average of filtered data you need an array formula.

=AVERAGE(IF(SUBTOTAL(3,OFFSET(L7:L22800,ROW(L7:L22800)-ROW(L7),0,1)),IF(L7:L22800="Car",M7:M22800)))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.