Average SumProduct

[rzrich]

I am trying to use the same priciple behind sumproduct since I have a lot of moving variables in my table and reference that need to be matched. I have the sum products working fine (see example) because they are actually finding sums. I need to do an average of the CM/ST and CM/MT since there is multiple of these that shouldn't be added up but averaged (Cell C3 and C5). Please see below:

Data Set
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Short Tons[/TD]
[TD]Short Tons[/TD]
[TD]CM/ST[/TD]
[TD]SM/ST[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Person A[/TD]
[TD]Person B[/TD]
[TD]Person A[/TD]
[TD]Person B[/TD]
[/TR]
[TR]
[TD]Product A
[/TD]
[TD]175.234[/TD]
[TD]0
[/TD]
[TD]3554.516[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD]65.92352[/TD]
[TD]0[/TD]
[TD]362[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD]195.676[/TD]
[TD]0[/TD]
[TD]468[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]
Summary Table

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Total Product[/TD]
[TD]Person A[/TD]
[TD]Person B<strike></strike>
[/TD]
[/TR]
[TR]
[TD]Short Tons[/TD]
[TD]

=IF($B$2="Total Product",SUMPRODUCT((Prophix!$E$2:$AU$2='Sales Members'!$B3)*(Prophix!$E$3:$AU$3='Sales Members'!C$2)*(Prophix!$C$4:$C$44='Sales Members'!$B$2),Prophix!$E$4:$AU$44),SUMPRODUCT((Prophix!$E$2:$AU$2='Sales Members'!$B3)*(Prophix!$E$3:$AU$3='Sales Members'!C$2)*(Prophix!$D$4:$D$44='Sales Members'!$B$2)))​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[/TR]
[TR]
[TD]CM/ST[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Metric Tons[/TD]
[TD]

=IF($B$2="Total Product",SUMPRODUCT((Prophix!$E$2:$AU$2='Sales Members'!$B3)*(Prophix!$E$3:$AU$3='Sales Members'!C$2)*(Prophix!$C$4:$C$44='Sales Members'!$B$2),Prophix!$E$4:$AU$44),SUMPRODUCT((Prophix!$E$2:$AU$2='Sales Members'!$B3)*(Prophix!$E$3:$AU$3='Sales Members'!C$2)*(Prophix!$D$4:$D$44='Sales Members'!$B$2)))​

<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike><strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[/TR]
[TR]
[TD]CM/MT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have tried to do a countif formula to divide the sumproduct by to get the average but it wasn't working properly either due to the multiple headings I am trying to match it to. I am up for any kind of help.