Subtotal combined with AverageIF

[sTremper]

Can you tell me how to use Subtotal and AverageIF? I want to subtotal the average but only positive numbers.

I can use Subtotal(Average... but I want Subtotal(AverageIF(xxx:xxx,">0").

Help please.

 

[T. Valko]

Try this array formula**:

=AVERAGE(IF(SUBTOTAL(2,OFFSET(A5,ROW(A5:A100)-ROW(A5),0,1))>0,A5:A100))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Adjust the range to suit.

 

[sTremper]

Thanks for the reply. I will have to try it later -- past my bedtime.
Have a great and safe holiday weekend.
tks

 

[T. Valko]

I misunderstood your requirement.

Try this version:

=AVERAGE(IF(SUBTOTAL(9,OFFSET(A5,ROW(A5:A100)-ROW(A5),0,1))>0,A5:A100))

Still array entered!

 

[sTremper]

Thanks T. Valko!
This site is great. Your code worked well. Thanks again.
Steve

 

[T. Valko]

You're welcome! Thanks for the feedback.

 

[sTremper]

Well this is working correctly but now I have noticed that not all of my rows apply as they don't fit the criteria (e.g., row is only for data entry and not to be used in average calculation.) Is there a way to IF B2=X then ignore?