Formula for Inventory using one quantity in multiple locations

Hi,
I have an inventory to try and determine how many RAW parts I need to order, FIN (parts made), WIP (parts available to be made), upcoming orders, RAW (same component in products a,b,c). I need a formula or function to take the FIN subtract the WIP and add the upcoming requirements to see how many raw parts are needed. But I need it to take into account if the number WIP has already been subtracted by one of the other components.

So for example

FIN
a = 15
b = 8
c = 81

WIP (part on hand to be divided and used in A,B,C)
=54

Upcoming orders
a=20
b=8
c=437

Raw Required
a=0 (15 in fin, 5 from WIP)
b= 0 (covered in FIN)
c= 307 (81 from FIN, 49 from WIP)

I'm not sure if I'm explaining this correctly, but for the RAW required C would need the formula to calculate the 81 in fin, subtract the 49 remaining in WIP realizing the 5 was taken for A.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

It would also be helpful if you could use the same details in your explaination as you have in your sample, it is not always obvious what is being referred to if the descriptions do not match the raw data provided.

To make what you want work you will need to add an extra column to your table for a preliminary calculation.
You will also need to UNMERGE the cells in column D and enter the colour into each individual cell. When you merge cells, only the top left cell of the merge range contains what you enter, the others are blank. So in your example, looking at the merged cell D2:D4, only D2 contains the word 'Black' D3 and D4 are empty, so the formula will not evaluate correctly.

Whilst the merged cells in column D need to be removed, those in column E can be used to your advantage.

Once you have unmerged the cells in column D and filled in the empty cells. Enter this formula into row 2 of your additional column.

=MAX(0,K2-B2)

Then use this formula in F2 to calculate the RAW parts needed. Note that the range G$2:G2 refers to the formula above, you will need to change that if you put that formula in a different column. Note that the $ symbol is only used in front of the first row.

=ABS(MIN(0,LOOKUP(1E+100,E$2:E2)-SUMIF(D$2:D2,D2,G$2:G2)))

Once entered, fill both formulas down as needed.

edit:- this was based on your original screen image from post #1. looking at your sample file, I think that it might be incorrect.

Thank you for all your help! I have figured out how to properly confirm, but it seem now to be creating a sum so instead of having the A=31, B=50, C=185. I have A=31, B=81, C=266?