IF statements:SUMIF, SUMIFS and Ranges

Hi all, I have not done excel programming since high school and have forgotten most of it. Even though this is the case I have be charged with creating at formula at work to work out the amount of stock we are using. My brief is to create a formula which will take a stated amount from the amount of stock when an amount is typed into a spreadsheet. It gets more difficult as there are 10 different pipes in stock and so the amount typed must be taken from the correct pipes stock.

So far i have created a form which works with a SUMIF and would work if we only used 1 type of pipe for each job, but due to the fact that we use up to 6 types the sum if is not suitable as you can only use 4 different ranges.

So if anyone could help it would be very much appreciated

I have attached the EXCEL document for you to look at

Kindest Regards
Gary

Thanks Richard that was amazing, it works really well.

Kindest Regards and Thank you again

Gary