Add average calculation in Sumproduct

[rthakur]

Greetings all!

Working Formula:
=SUMPRODUCT(--(GNC70901!$A$2:$A$20000<>""),--(GNC70901!$F$2:$F$20000=$B$3),--(LEFT(GNC70901!$C$2:$C$20000,2)<>"ER"),--(LEFT(GNC70901!$C$2:$C$20000,2)<>"ES"),--(GNC70901!$H$2:$H$20000>30))

Above formula gives me the total > 30.

I would like to calculate the average of the all the counts >30

Not sure how and where to add this array....thanks.

Regards,

rthakur

 

[mvptomlinson]

Perhaps:

=AVERAGE(IF(($A$2:$A$10<>"")*($F$2:$F$10=$B$3)*(LEFT($C$2:$C$10,2)<>"ER")*(LEFT($C$2:$C$10,2)<>"ES")*($H$2:$H$10>30),$H$2:$H$10))

Since it's an array formula, press CTRL+SHIFT+ENTER after typing it.

 

[dafan]

=SUMPRODUCT(--(GNC70901!$A$2:$A$20000<>""),--(GNC70901!$F$2:$F$20000=$B$3),--(LEFT(GNC70901!$C$2:$C$20000,2)<>"ER"),--(LEFT(GNC70901!$C$2:$C$20000,2)<>"ES"),--(GNC70901!$H$2:$H$20000>30))/COUNTIF(GNC70901!$H$2:$H$20000,">30")

Something like that?