Explanation
This formula demonstrates a very simple inventory concept where current inventory is simply the result of all incoming stock minus all outgoing stock.
In the example, colors are treated as unique item identifiers imagine a product available in one size only in just three colors: red, blue, or green.
The key to this approach is to useExcel 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.
Related formulas
Subtotal by color
SUMIFS with Excel Table
Count sold and remaining
Related functions
SUMIFS Function
The Excel SUMIFS function returns the sum of cells that meet multiple conditions, referred to as criteria.
To define criteria, SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be used with cells that contain dates, numbers, and text.
Related videos
How to build a simple summary table
Introduction to structured references
How to use SUMIFS with an Excel Table
Formulas to query a table
