Sumproduct of Sumproduct / or / Sumproduct of Sumifs

yayo12

New Member
Joined
May 26, 2008
Messages
4
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

ProductProd DescChannelQty M01Qty M02Price M01Price M02
Ball
round
store58$10$20
Ballroundonline68$15$30
Ballyellowstore36$5$15
Ballredonline48$2$20

<tbody>
</tbody>

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:

ProductChannelQty M01Qty M02Price M01
Ballstore814??
BallOnline1016??

<tbody>
</tbody>

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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Try:
Excel Workbook
ABCDEFG
1ProductProd DescChannelQty M01Qty M02Price M01Price M02
2Ballroundstore58$10$20
3Ballroundonline68$15$30
4Ballyellowstore36$5$15
5Ballredonline48$2$20
6
7ProductChannelQty M01Qty M02Price M01
8Ballstore81465
9BallOnline101698
Sheet
 
Upvote 0
Thank you so much
Worked perfectly. I sometimes forget that simplicity is key! :)
I was trying something utterly complicated.
 
Upvote 0
You're welcome. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top