Help using conditional sumproduct for weighted average to get avg COGS

[jscaggs]

Hello and thank you in advance for any help you can provide with my scenario below:

I have a multi sheet workbook with the following breakdown:

Receiving - Here we receive products and enter model, qty, and cost. Each day we add additional items to this sheet and want them added to current inventory.

Shipping - Here we log model shipped, qty shipped and sale price. Each day we add additional items to this sheet and want them deducted from the inventory left in stock.

Current Inventory - Here we have our master list of products and need our AVG COGS as well as qty available.


To calculate average COGS I am using a formula such as the following:

=(IF(J2:J5000="Blackberry 8700c Needs Repair",SUMPRODUCT(K2:K5000,L2:L5000/SUM(K2:K5000))))


The issue here is that I want my AVG COGS to look at the entire receiving sheet and if the model name matches the model name on the Current Inventory line item then I want it to check the qty received and the cost to accurately adjust my AVG COGS.

Now it is doing the weighted average part right for my COGS but it is not excluding the values where the model name doesn't match up.

Please any help appreciated!!!

Thanks,

 

[Aladin Akyurek]

Are you after?...

=SUMPRODUCT(--(J2:J5000="Blackberry 8700c Needs Repair"),K2:K5000,L2:L5000)/
    SUMIF(J2:J5000,"Blackberry 8700c Needs Repair",K2:K5000)

 

[jscaggs]

Yes I just tested the formula you gave me and it seems to be working perfectly!!!



Will this formula work if I have it looking at a range that is located in a different worksheet? Basically I'm asking if it could look something like this:

='Receiving Log'!J3:J5000 instead of just J:3:J5000


I will continue building and post any troubles I am having.

This board is great.

Again thank you very much!!

 

[Aladin Akyurek]

Yes I just tested the formula you gave me and it seems to be working perfectly!!!

You are welcome.

Will this formula work if I have it looking at a range that is located in a different worksheet? Basically I'm asking if it could look something like this:

='Receiving Log'!J3:J5000 instead of just J:3:J5000

I will continue building and post any troubles I am having.

This board is great.

Again thank you very much!!

Yes, it should work with sheet refs ...