I have an Excel table as follows:
Color | Inventory Blue | 180 Red | 50 Green | 10
I want to look up the inventory of the color that I would require depending which product I'm going to use and then subtract if it was used on a past line, my table would look like this:
Item | Color | Usage | Inventory Left ABC | Blue | 50 | 130 BCD | Blue | 30 | 100 CDE | Blue | 50 | 50 EFG | Red | 25 | 25 FGH | Red | 10 | 15 GHI | Green | 5 | 5
How could I do it with an Excel formula?
I was thinking in some type of SUMIF with OFFSET but I can't figure it out.