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
<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:
<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
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
Product | Prod Desc | Channel | Qty M01 | Qty M02 | Price M01 | Price M02 |
Ball | round | store | 5 | 8 | $10 | $20 |
Ball | round | online | 6 | 8 | $15 | $30 |
Ball | yellow | store | 3 | 6 | $5 | $15 |
Ball | red | online | 4 | 8 | $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:
Product | Channel | Qty M01 | Qty M02 | Price M01 |
Ball | store | 8 | 14 | ?? |
Ball | Online | 10 | 16 | ?? |
<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