Index, Match, Average using Active Cell

[kipper19]

Hi all,
Wondering if someone would know if its possible to use the Average function with Index Match using my below Active Cell code, I use this code a lot in spreadsheets but it has me stumped on how to incorporate the Average function.
Basically what I want the code to do is look in column B for the number represented in Column E then average the figures in Column C against that particular number

Range("D" & Trim(Str(rr.Row))).Select

ActiveCell.FormulaArray = _

"=IFERROR(INDEX(Sheet1!R3C3:Sheet1!R" & Trim(Str(totAvg)) & "C3,MATCH(1,(Sheet1!R3C1:Sheet1!R" & Trim(Str(totAvg)) & "C1=Sheet1!R1C1)*(Sheet1!R3C5:Sheet1!R" & Trim(Str(totAvg)) & "C5=RC[-3]),0)),0)"

 

[Legacy 143009]

For example F3:

=SUMPRODUCT(--(($B$3:$B$11)=$E3),$C$3:$C$11/COUNTIF($B$3:$B$11,$E3))

 

[kipper19]

Flashbond,
Thanks for the reply, how would I also incorporate into this formula that A3:A11 = A1

 

[Legacy 143009]

Like this?

=SUMPRODUCT(--(($A$3:$A$11)=$A$1)*--(($B$3:$B$11)=$E3),$C$3:$C$11)/COUNTIF($B$3:$B$11,$E3)

 

[kipper19]

Thanks for your help, appreciate it

 

[Legacy 143009]

I am glad it did help! Thanks for the feedback

 

[Legacy 143009]

By the way this could also be an alternative according to your needs:

=SUMPRODUCT(--(($A$3:$A$11)=$A$1)*--(($B$3:$B$11)=$E3),$C$3:$C$11)/COUNTIF($B$3:$B$11,$E3,$A$3:$A$11,$A$1)