Average SumProduct

rzrich

New Member
Joined
Sep 26, 2018
Messages
3
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
DEFKL
Short TonsShort TonsCM/STSM/ST
Person APerson BPerson APerson B
Product A
175.2340
3554.5160
Product A65.9235203620
Product B195.67604680
Product C0000

<tbody>
</tbody>
Summary Table

BCD
Total ProductPerson APerson 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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,099,696
Messages
5,470,221
Members
406,686
Latest member
BNR_ 1980

This Week's Hot Topics

Top