AVERAGEIF for visible rows only

[Excel_BWL]

https://i.gyazo.com/2a47fd1b810edc436adb16cac4f2d256.png

I want to set up a function which can average, for example, all the durations corresponding to zone "B", but ignoring any hidden rows which include times for zone "B".

AVERAGEIF doesn't work because it includes hidden rows, and SUBTOTAL doesn't have an option for AVERAGEIF.

Would appreciate some advice?

Cheers

 

[Excel_BWL]

Not zone B, say zone D1 for example (just realised in this screenshot there is no B). Principle should still be the same though

Thanks

 

[Jonmo1]

I suggest a helper column to put the Subtotal function in.

Say column C or whatever, in C2 and filled down put
=SUBTOTAL(102,B2)
Where Column B contains the numbers being averaged, and column A is your zones.

Then add a criteria to averageifs to test column C for 1.

AVERAGEIFS(B:B,A:A,"Zone B",C:C,1)

Averages column B, where Column A = "Zone B" and Column C = 1
Column C = 1 if there is a number in it, AND it is a visible row, otherwise column C = 0

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A2:A25,ROW(A2:A25)-ROW(A2),0,1)),B2:B25,--(A2:A25="B"))/SUMPRODUCT(SUBTOTAL(103,OFFSET(A2:A25,ROW(A2:A25)-ROW(A2),0,1)),--(A2:A25="B"))

M.

 

[Excel_BWL]

Maybe...

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A2:A25,ROW(A2:A25)-ROW(A2),0,1)),B2:B25,--(A2:A25="B"))/SUMPRODUCT(SUBTOTAL(103,OFFSET(A2:A25,ROW(A2:A25)-ROW(A2),0,1)),--(A2:A25="B"))

M.

You are my new best friend

Thanks so much

 

[Marcelo Branco]

You are welcome. Glad to help

M.