average of visible cells in a filtered range

[Guest]

Good Afternoon:

I need to average only the visible cells in a range which has been filtered,
i.e.

=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<>0)).

I now want to average the age of the victims. I've tried several functions,
including =Average(sumproduct....),
=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<>0))/COUNTIF(Age_2005,"<>0"), and some others.

TIA for your assistance.

 

[Guest]

If the range is filtered with either Autofilter or Advanced Filter, you
should check Excel Help for the SUBTOTAL function.

That function will Sum, Average, etc on only the visible items in a filtered
list.

See if that works.

 

[Guest]

=subtotal(1,age column)

will work on (average) only the visible cells

 

[Aladin Akyurek]

Good Afternoon:

I need to average only the visible cells in a range which has been filtered,
i.e.

=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<>0)).

I now want to average the age of the victims. I've tried several functions,
including =Average(sumproduct....),
=SUMPRODUCT(--(InjuryType1_2005="Gunshot"),--(Age_2005<>0))/COUNTIF(Age_2005,"<>0"), and some others.

TIA for your assistance.

What are the real ranges and on which range is autofiltering is applied?

 

[Guest]

This is one option
=AVERAGE(IF((InjuryType1_2005="Gunshot")*(Age_2005<>0),Age_2005))

Confirm the Array-formula with Ctrl + Shift then hit Enter

Hope it helped
Ola Sandström

 

[Guest]

Many thanks.

=subtotal(1,age column)

will work on (average) only the visible cells