Subtotal of averageif

[cdockins]

Hello,

How would I create a Subtotal formula for an Averageif equation? Is this possible do I need an array formula?

Any help is appreciated, Thanks!

 

[barry houdini]

Hello cdockins, welcome to MrExcel

Not quite sure what you want but this array formula will average visible cells (after filtering) in B2:B100 where A2:A100 = "x"

=AVERAGE(IF(SUBTOTAL(2,OFFSET(B2,ROW(B2:B100)-ROW(B2),0)),IF(A2:A100="x",B2:B100)))

confirmed with CTRL+SHIFT+ENTER

is that the sort of thing you mean?

 

[cdockins]

Thanks for your response! I tried the equation you gave me which I plugged in like this =AVERAGE(IF(SUBTOTAL(2,OFFSET(G14,ROW(G13:G263)-ROW(G14),0)),IF(I13:I185=F5,G13:G263))) and got a #NA response.

What I am trying to do is find the average for Days on Market (I2) for both 7% (I3) and 6% (I4) commission. I want to use subtotal so it will calculate when more info is added and when it is filtered. What was the OFFSET in your previous equation used for? Did I plug in the wrong cells or do I need a different equation?

Thanks!

 

[barry houdini]

You'd need to make the range references consistent, so if data is in rows 13 to 263 I think the formula should be like this:

=AVERAGE(IF(SUBTOTAL(2,OFFSET(G13,ROW(G13:G263)-ROW(G13),0)),IF(I13:I263=F5,G13:G263)))

That's an "array formula" which needs to be entered in a specific way. Put the formula in the cell and then press F2 to slect formula, hold down CTR and SHIFT keys and press ENTER so that curly braces like { and } appear around the formula in the formula bar.

OFFSET splits the range into individual cells which can then be examined by SUBTOTAL, that's used to only include visible rows after filtering.

What's in F5, that seems to be empty in your screenshot?

 

[cdockins]

Yeah I had some merged cells that I didn't account when I told you the cells. Anyways, the formula works great thanks a lot for your help!