Averageif for only visible cells

[excelnabb]

Hi

I'm trying to make a averageif taking into account only cells that are visible. This is the formula =AVERAGEIF(HI20:HI9991,">0")
I have tried with subtotal, sumproduct and so on, but apparently I don't get it to work.

[XLent]

I don't think you can use AVERAGEIF in SUBTOTAL context, at least not yet...

the below Array should do what you need?

=AVERAGE(IF(SUBTOTAL(109,OFFSET(HI20,ROW(HI20:HI9991)-ROW(HI20),0))>0,HI20:HI9991))
confirmed with CTRL + SHIFT + ENTER

if the array is set correctly the above will appear encased within { } -- these cannot be added manually.

edit:

as a *tip* -- if you're doing lots of calculations based on filtered data it is, in my opinion, good practice to create a 1/0 flag (in a spare column) to denote row visibility, e.g.

HZ20: =SUBTOTAL(3,A20)
copied down

you can then use this column (with criteria of 1) to isolate visible rows -- without needing to use expensive calcs like the above (i.e Volatile Array)

[excelnabb]

Great! thank you so much!

[excelnabb]

I tried the same and changing this formula:

=SUMIF(HJ20:HJ9991,">1")

to this one:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(HJ20,ROW(HJ20:HJ9991)-ROW(HJ20),0))>1,HJ20:HJ9991)

but I just get 0...?

[Richard Buttrey]

Why not just

Please Login or Register  to view this content.

[excelnabb]

It doesn't work, I get 5.7 with that formula. I should get 741. It must take into account that it should be above 1 and not take into account hidden cells that are filtered

[XLent]

Hi, sorry, just reading this...

You're saying you've added the 1/0 {visible flag formula} to column HJ?

If so you can then replace the Array with an AVERAGEIFS using new column [HJ] as the 2nd criteria (=1 - i.e. visible row)

=AVERAGEIFS(HI20:HI9991,HI20:HI9991,">0",HJ20:HJ9991,1)

[Richard Buttrey]

Please upload the workbook so that we can see in context

[XLent]

edit: if you want to SUM col HJ where > 1 and visible then the change is to coerce you boolean array:

=SUMPRODUCT(--(SUBTOTAL(109,OFFSET(HJ20,ROW(HJ20:HJ9991)-ROW(HJ20),0))>1),HJ20:HJ9991)

but I reiterate my earlier point - the fact you're doing multiple calcs using visible rows means, for efficiency, you should really add the single visible flag calc in a spare column
with the above in place your calcs become trivial (and non-volatile) - e.g. AVERAGE calc (per prior post) whilst your SUM becomes a simple SUMIFS - i.e. SUMIFS(number_range,number_range,">1",visible_range,1)

[Richard Buttrey]

It doesn't work, I get 5.7 with that formula. I should get 741. It must take into account that it should be above 1 and not take into account hidden cells that are filtered

One pragmatic solution would be to use a helper column that determined which rows to hide
e.g.

Please Login or Register  to view this content.

Then filter on he helper column and use the formula I gave you.

[excelnabb]

This one worked perfectly!
=SUMPRODUCT(--(SUBTOTAL(109,OFFSET(HJ20,ROW(HJ20:HJ9991)-ROW(HJ20),0))>1),HJ20:HJ9991)
thank you to you all!