Sumproduct of Sumproduct / or / Sumproduct of Sumifs

[yayo12]

Hello guys,

I have been working on a file for quite sometime, and I finally hit the wall. I simply cannot find a workaround to the following question.

I have an original table that i was able to summarize using index(match) so now the new table looks something like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Prod Desc[/TD]
[TD]Channel[/TD]
[TD]Qty M01[/TD]
[TD]Qty M02[/TD]
[TD]Price M01[/TD]
[TD]Price M02[/TD]
[/TR]
[TR]
[TD]Ball[/TD]
[TD]
round[/TD]
[TD]store[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]$10[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD]Ball[/TD]
[TD]round[/TD]
[TD]online[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]$15[/TD]
[TD]$30[/TD]
[/TR]
[TR]
[TD]Ball[/TD]
[TD]yellow[/TD]
[TD]store[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]$5[/TD]
[TD]$15[/TD]
[/TR]
[TR]
[TD]Ball[/TD]
[TD]red[/TD]
[TD]online[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]$2[/TD]
[TD]$20[/TD]
[/TR]
</tbody>[/TABLE]

Ok, so now that I have the table above, I now want to summarize even more by Product and Channel, hence, my new table looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Channel[/TD]
[TD]Qty M01[/TD]
[TD]Qty M02[/TD]
[TD]Price M01[/TD]
[/TR]
[TR]
[TD]Ball[/TD]
[TD]store[/TD]
[TD]8[/TD]
[TD]14[/TD]
[TD]??[/TD]
[/TR]
[TR]
[TD]Ball[/TD]
[TD]Online[/TD]
[TD]10[/TD]
[TD]16[/TD]
[TD]??[/TD]
[/TR]
</tbody>[/TABLE]

I used sumproduct and sumifs(index(match) and both formulas worked great for the quantity on columns "Qty M01 and Qty M02" since this is a simple addition of units.

My issue is on the price, since I need a weighted average of price per units.
I have tried every single variation of nesting sumproducts or sumproduct(sumif) and they all show the same result. Both formulas are actually multiplying the result of qty * price
In the above example what I need the formula to do is what a usual sumproduct would do (5*$10)+(3*$5) = 65
Yet my result is (5+3)*($10+$5) = 120

What formula can I use?
Thank you so much

 

[Tetra201]

Maybe

=SUMPRODUCT(--(C2:C5="Store"),D2:D5,F2:F5)

 

[AhoyNC]

Try:

Excel Workbook
	A	B	C	D	E	F	G
1	Product	Prod Desc	Channel	Qty M01	Qty M02	Price M01	Price M02
2	Ball	round	store	5	8	$10	$20
3	Ball	round	online	6	8	$15	$30
4	Ball	yellow	store	3	6	$5	$15
5	Ball	red	online	4	8	$2	$20
6
7	Product	Channel	Qty M01	Qty M02	Price M01
8	Ball	store	8	14	65
9	Ball	Online	10	16	98
Sheet

 

[yayo12]

Thank you so much
Worked perfectly. I sometimes forget that simplicity is key!
I was trying something utterly complicated.

 

[AhoyNC]

You're welcome. Thanks for the feedback.