Sumproduct calculation

here is an updated sheet with descriptions

calculations required in J5 and O5 - the manual inputs are in columns A, C and D as stock comes in and goes out

Cheers JD

Brilliant!

I also need it to display maximum opex stock out once the correct stock has been exhausted at present if you take 10 items out of capex and then 60 out of opex it displays -10 in the opex running stock column - is there any way to max out the level at 0 once the stock has been fully used?

Still goes over the maximum allowable amount and puts -10 in K5

Thanks works a treat

Hi the calculation works a treat however there is a side-effect that I cant seem to rectify -

The problem I have is that when you then put stock into the capex stock in column it then changes the opex stock out and adjusts it to whatever you have entered in the stock in cloumn.

Example - if you have 10 of 001 out of the capex stock to reduce it to zero and the next stock out is 50 to reduce the opex stock level also to zero the next time you enter new stock into capex as incoming stock it automatically alters the opex stock out cloumn (K5) and adjusts it to whatever you have enetered.

I need both coloumns to work independantly of each other once the initial process is done.

I think thats what im trying to achieve - because as soon as I enter some more capex stock into the stock holding it changes the figures in the opex stock. Please see attached sheet for details.