average of visible cells in a filtered range

D

dave roth

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.
 
R

Ron Coderre

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.
 
A

Aladin Akyurek

dave said:
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.
Click to expand...

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

Ola

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
 
Top