# Monthly Weighted Average Cost for Multiple Materials by Vendor

#### Mix1325

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

#### Attachments

• 16.4 KB Views: 14
Last edited:
• Hui

#### Hui

##### Excel Ninja
Staff member
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

Last edited:

#### Mix1325

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

#### Attachments

• 16.6 KB Views: 5
Last edited:

#### Hui

##### Excel Ninja
Staff member
Mix1325

Yes, Adding the helper column simplifies the solution substantially

I had assumed that that wasn't an option?

• Mix1325

Thank you, Hui!

#### Attachments

• 10.3 KB Views: 10
Last edited:

#### Mix1325

##### New Member
Thank you, Bosco!