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

D	E	F	K	L
	Short Tons	Short Tons	CM/ST	SM/ST
	Person A	Person B	Person A	Person B
Product A	175.234	0	3554.516	0
Product A	65.92352	0	362	0
Product B	195.676	0	468	0
Product C	0	0	0	0

<tbody>
</tbody>

Summary Table

B	C	D
Total Product	Person A	Person B<strike></strike>
Short Tons	=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>	<strike></strike>
CM/ST
Metric Tons	=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>	<strike></strike>
CM/MT

<tbody>
</tbody>

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.