Monthly Weighted Average Cost for Multiple Materials by Vendor

[Mix1325]

Hello,
I have a list of materials purchased in various quantities from various vendors and a separate list of monthly costs for each particular material purchased.

My goal is to determine the weighted average cost per unit of purchases from a particular vendor during each month. To do so, I believe the following steps should be performed.

1. Calculate the total purchases made from the vendor of interest for the desired month
2. Calculate the weight of each component/material purchased from the total purchases calculated in Step 1
3. Multiply the weight of each component/material calculated in Step 2 by the corresponding cost included in the list of costs
4. Sum the results obtained in Step 3 to arrive to the weighted average cost for all the purchases made from the vendor of interest

Please note that the cost for each material/vendor is constant during a month. It only changes on a monthly basis.
The main issue is that I would like to make this calculation in a single cell based on Vendor and Month-Year if possible and I would like to avoid using VB. I have included an example for illustration purposes.

Thank you in advance for all your help and support in this matter.

 

[Hui]

Mix

This is a very good question.

It is rare that we actually get new and challenging questions, so I appreciate it.

I hope some of the other Ninja's and others in general will have a go as I am sure there are easier ways to solve this than the way I have

But solve it I have:

The Total Cost is:
=SUMPRODUCT(($D$3:$D$9)*($A$3:$A$9=$M$3)*($C$3:$C$9=$N$3)*TRANSPOSE(($H$3:$H$8=TRANSPOSE($B$3:$B$9))*($G$3:$G$8=TRANSPOSE($A$3:$A$9))*($J$3:$J$8=TRANSPOSE($C$3:$C$9))*($I$3:$I$8))) Ctrl+Alt+Enter

The Weighted Average is
=SUMPRODUCT(($D$3:$D$9)*($A$3:$A$9=$M$3)*($C$3:$C$9=$N$3)*TRANSPOSE(($H$3:$H$8=TRANSPOSE($B$3:$B$9))*($G$3:$G$8=TRANSPOSE($A$3:$A$9))*($J$3:$J$8=TRANSPOSE($C$3:$C$9))*($I$3:$I$8)))/SUMIFS($D$3:$D$9,$A$3:$A$9,$M$3,$C$3:$C$9,$N$3) Ctrl+Alt+Enter

 

[Mix1325]

Thank you for your kind words and the solution provided, Hui. I believe your formulas work but, in the meantime, I believe I found a simpler solution by using the so called conditional sumproduct - formula included below. It took some further research on the internet, but I believe it provides the correct result.
=SUMPRODUCT(--(C3:C9="ZZ")*(A3:A9="Aug-19"),D3:D9,F3:F9)/SUMIFS(D3:D9, C3:C9,C4,A3:A9,A4)

 

[Hui]

Mix1325

Yes, Adding the helper column simplifies the solution substantially

I had assumed that that wasn't an option?

 

[Mix1325]

Thank you, Hui!

 

[bosco_yip]

Another option for Weighted Average Cost in difference size tables

In O4, enter array formula (CTRL+SHIFT+ENTER)

=SUM(SUMIFS(I$3:I$9,G$3:G$9,M3,J$3:J$9,N3,H$3:H$9,IF(A$3:A$9&C$3:C$9=M3&N3,B$3:B$9))*D$3:D$9)/SUMIFS(D$3:D$9, A$3:A$9,M3,C$3:C$9,N3)

Regards
Bosco

 

[Mix1325]

Thank you, Bosco!