Trying to get averageif subtotal to work with filter

[Chris333]

Hello,

I am wondering if anyone could help me out. I'm trying to get the average of Column D based of the status of Column A and still apply a working filter. I can either get my averages to match correctly with with the status of column A or get the averages to work with the filter. I cannot seem to get the average(if(subtotal to all work together. Here is the formula I am attempting to use =AVERAGE(IF(SUBTOTAL(9,OFFSET(D13,ROW(D13:D2603)-ROW(D13),0,1)),--(A13:A2603="Sold"))). I kind of pieced this together from a bunch of different sources from around the web since I couldn't find the exact solution I was looking for. The formula doesn't even seem to recognize the data in column D. If I switch my status from " sold" to "Canceled" in column A, the highlighted cell seems to count backward from 100%. Could anyone please assist me with this formula?

Thank you,

 

[Fluff]

Hi & welcome to MrExcel.
Why not use a helper column with

=SUBTOTAL(3,A13)

and then you can use the averageifs function like

=AVERAGEIFS(D13:D2603,A13:A2603,"Sold",H13:H2603,1)

just change the H column to where you have the subtotal formula

 

[Chris333]

Hi & welcome to MrExcel.
Why not use a helper column with

=SUBTOTAL(3,A13)

and then you can use the averageifs function like

=AVERAGEIFS(D13:D2603,A13:A2603,"Sold",H13:H2603,1)

just change the H column to where you have the subtotal formula

Hi!
Thank you for the response back, I will give this a try.

 

[Fluff]

Ok

 

[Chris333]

Ok

Hello,

I took your advice and did end up pursing a helper column. It was a bit complicated but in the end I did get it to work the way I wanted to. Thanks again for the advice!!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[floW_5]

Hi & welcome to MrExcel.
Why not use a helper column with

=SUBTOTAL(3,A13)

and then you can use the averageifs function like

=AVERAGEIFS(D13:D2603,A13:A2603,"Sold",H13:H2603,1)

just change the H column to where you have the subtotal formula

Hey Fluff, is there a way to use this formula with AVERAGE(IF( separate? Reason why is I have a criteria that seems to only work with a separate average and if.

Here is the formula

=IFERROR(AVERAGE(IF(C2:C11>D2:D11,C2:C11,D2:D11)),""))

Lets just say the helper column will be in E2:E11, how would I add that to this current condition formula?

 

[Fluff]

As this is a different question, you need to start a thread of your own.
Thanks

 

[floW_5]

As this is a different question, you need to start a thread of your own.
Thanks

Oh I see, I was trying to avoid that because not sure how the people on here are with seeing the same multiple, repetitive or similar question threads being posted. Just trying to save that dull reaction.
But if that's not an issue, Ill quickly do little more research myself then I'll start that new thread. (just trying to do as much myself before I let someone spoon feeds me again)
Thanks for the response.