Subtotal Average Function - Conditional based on value in a specific column

Hi All,

I'm trying to conduct an average of prices based on filtered data, only if a specific column is not equal to a specific letter.

Example : Column A contains Prices. Column B contains a purchase code.

I want to be able to conduct an average of prices based on what the user has filtered on, so I'm using the subtotal average function. I only want the data to calculate if Column B is not equal to the letter "E".

Can anybody help? Been having major difficulty with this. Thanks!

Originally Posted by daddylonglegs

The simple way to do this is with a helper column, e.g. In a blank column like column Z use this formula in Z2 copied down:

=SUBTOTAL(2,A2)

Then the formula for averaging with your condition but only visible rows would be

=AVERAGEIFS(A:A,B:B,"<>E",Z:Z,1)

If you don't want a helper column try an array formula like this

=AVERAGE(IF(B$2:B$100<>"E",IF(SUBTOTAL(2,OFFSET(A$2,ROW(A$2:A$100)-ROW(A$2),0)),A$2:A$100)))

Confirmed with CTRL+SHIFT+ENTER

adjust ranges as required

Thank you so much! This solved it. I'm trying to understand what the code is actually saying, but thank you so much for providing it!