Hi,

I wonder if anyone could offer some advice as to why I can’t get this excel spreadsheet to do the calculation I need.  

To explain:  If I were to add stock into L5 and the Quantity into L6 then in L7 it needs to work out the formula.  The Formula is effectivley if it’s in the table starting at L25 and is under the 200000 header then it needs to multiple the M5 figure by that amount.  If it’s under the 5000 Header then it needs to multiply M5 by that amount depending on what is set under product name.  

I have it working for the 200000 calculation but I can’t seem to be able to make it go any further.  The idea is it’s taking the amount of containers and turning the figure into milliliters for better stock staking. 

This works: 

=SUMPRODUCT(SUMIF(L5:L14,Table6[200000],M5)*Table6[[#Headers],[200000]])

This Doesn’t=SUMPRODUCT(SUMIF(L5:L14,Table6[200000],M5)*Table6[[#Headers],[200000]])=SUMPRODUCT(SUMIF(L5,Table7[5000],M5)*Table7[[#Headers],[5000]])esn’t:

​Hi, I wonder if anyone could offer some advice as to why I can’t get this excel spreadsheet to do the calculation I need.   To explain:  If I were to add stock into L5 and the Quantity into L6 then in L7 it needs to work out the formula.  The Formula is effectivley if it’s in the table starting at L25 and is under the 200000 header then it needs to multiple the M5 figure by that amount.  If it’s under the 5000 Header then it needs to multiply M5 by that amount depending on what is set under product name.   I have it working for the 200000 calculation but I can’t seem to be able to make it go any further.  The idea is it’s taking the amount of containers and turning the figure into milliliters for better stock staking.  This works: =SUMPRODUCT(SUMIF(L5:L14,Table6[200000],M5)*Table6[[#Headers],[200000]]) This Doesn’t=SUMPRODUCT(SUMIF(L5:L14,Table6[200000],M5)*Table6[[#Headers],[200000]])=SUMPRODUCT(SUMIF(L5,Table7[5000],M5)*Table7[[#Headers],[5000]])esn’t:   Read More

​