# Add average calculation in Sumproduct

#### rthakur

##### Board Regular
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

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### mvptomlinson

##### Well-known Member
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

##### Well-known Member
Code:
``=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?

Replies
3
Views
198
Replies
0
Views
306
Replies
2
Views
472
Replies
3
Views
499
Replies
6
Views
376

1,190,810
Messages
5,983,044
Members
439,816
Latest member
aggelos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back