Formula producing mixed results, may required VBA

[BrutalDawg]

I have a formula that is giving me very mixed results. In some instances it is working perfectly, in others giving me a completely undesired result.

What I am attempting to do is subtract the quantity Sheet1!G from the quantity in Sheet2!G if two conditions in same row are met. Example, if 10,10,10,10, were the per week order and sold 25, the the formula would show due for each order should be 0, 0, 5, 10,

lets take item 6000, original order was 204, and 300 were sent. 165 is the next weeks order, the formula should show 69(ideally but I couldn't get that to work) or the original 165. With my formula it is showing 0, However; lets look at item 2856. 153 were ordered, 160 were sent, the next order correctly shows 122.

I have attached a sheet with examples. 18264

[p45cal]

It's very difficult to grasp what you're after. In the attached is a guess.
I've used dates, but not 100% sure that I've used the right dates (nor am I sure that the dates in your sample data are realistic - the dates on sheet 1 span only 3 or 4 days, while on sheet2 the When dates span some 3 weeks, and those dates don't overlap the dates in sheet1 at all!).
I've highlighted in column L those results that are the same as your Hopeful result.
Look at the formulae in columns J and K too. If they are correct, ultimately they can be put into one cell.

[p45cal]

Be aware of the rules regarding cross-posting on the vast majority of sites such as these.
You have cross posted this same question at https://www.mrexcel.com/forum/excel-questions/989651-formula-not-working-correctly.html
You should have supplied a link yourself - and any other site you have cross posted this question to (ExcelForum?).
It's ultimateley to your benefit, because once you're discovered not providing links, those who have helped rarely help again.

Have a read of http://www.excelguru.ca/content.php?184 which goes through the whys and wherefores.