# Sumproduct of Sumproduct / or / Sumproduct of Sumifs

#### yayo12

##### New Member
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

 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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Tetra201

##### MrExcel MVP
Maybe

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

#### AhoyNC

##### Well-known Member
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
Thank you so much
Worked perfectly. I sometimes forget that simplicity is key!
I was trying something utterly complicated.

#### AhoyNC

##### Well-known Member
You're welcome. Thanks for the feedback.

Replies
14
Views
442
Replies
6
Views
274
Replies
5
Views
90
Replies
6
Views
106
Replies
2
Views
222

1,126,940
Messages
5,621,732
Members
415,853
Latest member
Newlife72

### 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.

### Which adblocker are you using?

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

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