excel - excel - Look for value and subtract if duplicated - answerstu - answerstu.com answerstu

excel - Look for value and subtract if duplicated

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.

1 Answer

  1. Elvis- Reply

    2019-11-14

    With a layout as shown, please try:

    =VLOOKUP(B9,$A$1:$B$6,2,0)-SUMIF(B$1:B9,B9,C$1:C9)
    

    SO33975085 example

    The principle would be the same but a different layout might be more convenient.

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>