Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

How to calculate current stock or inventory in Excel

This tutorial covers basic inventory formula.

To calculate current stock, or inventory, you can use Excel Tables with a formula based on the SUMIF function.




In the example shown, the formula in K7 is:


Where “In” is the Excel Table on the left, “Out” is the table in the middle.

Worked Example:   Sum if equal to either x or y in Excel

How this formula works

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.

Worked Example:   SUMIFS function: Description, Usage, Syntax, Examples and Explanation

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.

Worked Example:   Sum if cells contain both x and y in Excel

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.

Leave a Reply

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