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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows
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
 

yayo12

New Member
Joined
May 26, 2008
Messages
4
Thank you so much
Worked perfectly. I sometimes forget that simplicity is key! :)
I was trying something utterly complicated.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,749
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,354
Messages
5,624,183
Members
416,017
Latest member
moritz210

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
Top