SubTotal & SubProduct with Filter problem?

ScorpioCRO

New Member
Joined
Aug 2, 2015
Messages
5
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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,047
Messages
5,526,464
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top