Where “In” is the Excel Table on the left, “Out” is the table in the middle. The key to this approach is to use Excel Tables, because Table ranges automatically expand to handle changes in data. This means we can get a total of all incoming red items with: And a total of all outgoing red items with: In both cases, the SUMIFS function generates a total for all red items in each table. Then, as long as both tables are up to date and complete, we can get the current inventory of red items with the following formula: As the formula is copied down, we get current inventory for each color.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
