Help Needed With Tracking Inventory

I want to go ahead and thank you for any help in advance.

I have this scenario whre I'm trying to track my running inventory remaining in a column. Below that table I have another table that is showing how I'm going to package them together. However I couldn't come up with a way to do this with a formula. Does anyone know who to do this? I've attached the excel workbook and if you have any questions please let me know.

Thank You

Originally Posted by travis.cook21

I want to go ahead and thank you for any help in advance.

I have this scenario whre I'm trying to track my running inventory remaining in a column. Below that table I have another table that is showing how I'm going to package them together. However I couldn't come up with a way to do this with a formula. Does anyone know who to do this? I've attached the excel workbook and if you have any questions please let me know.

Thank You

You can see that I manually typed out how much to subtract in the formula (In the Total Items Left column) as I went along which isn't the most efficient way.

Try

=B2-SUMIF($A$23:$A$1000,A2,$D$23:$D$1000)

Copy down

I could not work out how you calculated your figures.

The above totals data in D23 down for item A2 and subtracts from inventory in B2

so for "Camo" is 100 items (40+60) leaving a balance of 50

Originally Posted by JohnTopley

Try

=B2-SUMIF($A$23:$A$1000,A2,$D$23:$D$1000)

Copy down

I could not work out how you calculated your figures.

The above totals data in D23 down for item A2 and subtracts from inventory in B2

so for "Camo" is 100 items (40+60) leaving a balance of 50

Thanks for the reply!

The way I calculated column C in the top table is by manually typing in each time how many items of that color was selected below. For example Black is listed in the bottom table twice. Once for Black-10 packs of 4 and once for Black & White-5 packs of 4. So starting out I have a total of 50 Black items then subtract 40 (10packs*4 each) and then subtract 10 (5 packs of 4 Black&White ((which means 2 blacks in each pack) * 2 blacks for each pack=10) which puts me at 0 remaining for Black.

That formula you gave me would return 10 remaining because it wouldn't pick up the Black & White Cell.