Using Subtotal (Average) with IF

[mgclements]

Hi All,
Struggling with this one, can someone help?

I have an excel table, columns B to E are used for data grouping (for slicing/filtering), columns F to AO for months. Row 3 through 131 for data.

I want to have a subtotal at the bottom of each column that averages the non-zero value according to the slicer/filters.

I have many cells that show '0' and the average is taking these into account which I do not want.

There doesn't see to be a SUBTOTALIF and other threads are too confusing for me.

I currently use =SUBTOTAL(1,[Jan-22]) to average the data in the column, but I need to add somehow to ignore '0' numbers.

HELP?

 

[Fluff]

Add a helper column (I've called it IsVisible) with this formula

=subtotal(103,B2)

change the range to look at a column that will always have a value.
An then you can use

=averageifs([jan-22],[jan-22],"<>0",[IsVisible],1)