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.