Hidden Rows and AVERAGEIF formula

[Paul Cunningham]

55 year old newbie - please be gentle.
I have a spread sheet in which I wish to use the formula AVERAGEIF on a column of data which includes +ve, -ve, zero and errors.
I wish to present the data in a series of cells relating the average to conditions in several columns. So one average could be related to a column that is say male or female (M or F) another average may be related to free school meals (Y or N) etc. All works well until I apply a filter on a column and the AVERAGIF function ignores the filter. I have also tried using the AGGREGATE function as this can be set to ignore error/hidden rows etc. but AGGREGATE only works with AVERAGE not AVERAGEIF.
I appreciate that I could filter the columns for male and female and or free school meals etc. and just show the result using AGGREGATE, but I wish to be able to compare the different averages. It is a large table and navigating it is an issue for some of the users whom it is intended for.
Any help really appreciated.

 

[barry houdini]

Hello Paul,

Welcome to MrExcel

The simplest approach here is to use a helper column, e.g. if the column to average is column B then in Z2 copied down use this formula

=SUBTOTAL(2,B2)

That will return a 1 only when B2 is not filtered out and B2 contains a number

Now use AVERAGEIFS and include an extra condition for column Z to be equal to 1, e.g.

=AVERAGEIFS(B:B,C:C,"M",Z:Z,1)

so that will average column B only when column C = "M" and the row is visible after filtering

 

[Paul Cunningham]

Hello Paul,

Welcome to MrExcel

The simplest approach here is to use a helper column, e.g. if the column to average is column B then in Z2 copied down use this formula

=SUBTOTAL(2,B2)

That will return a 1 only when B2 is not filtered out and B2 contains a number

Now use AVERAGEIFS and include an extra condition for column Z to be equal to 1, e.g.

=AVERAGEIFS(B:B,C:C,"M",Z:Z,1)

so that will average column B only when column C = "M" and the row is visible after filtering

Not sure if this is how I say thank you - but thank you Harry H works a treat

 

[Paul Cunningham]

Sorry I Barry H not Harry H