Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get last row in mixed data with blanks in Excel
  • Find closest match in Excel
  • How to create dynamic named range with OFFSET in Excel
  • Vlookup Examples in Excel
  • How to get relative column numbers in a range in Excel

Data Analysis Examples

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Number and Text Filters Examples in Excel
  • Excel Pie Chart
  • How to create Checklist in Excel
  • How to combine 2 or more chart types in a single chart in Excel

Data Validation Examples

  • Excel Data validation allow weekday only
  • Excel Data validation don’t exceed total
  • Excel Data validation require unique number
  • Prevent invalid data entering in specific cells
  • Excel Data validation whole percentage only

How to calculate current stock or inventory in Excel

by

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.

Formula

=SUMIFS(In[Qty],In[Color],A1)-SUMIFS(Out[Qty],Out[Color],A1)

Explanation

In the example shown, the formula in K7 is:

=SUMIFS(In[Qty],In[Color],J7)-SUMIFS(Out[Qty],Out[Color],J7)

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

Worked Example:   Sum if greater than 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:   Sum if cell contains text in another cell in Excel

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:

=SUMIFS(In[Qty],In[Color],J7)

And a total of all outgoing red items with:

=SUMIFS(Out[Qty],Out[Color],J7)

In both cases, the SUMIFS function generates a total for all red items in each table.

Worked Example:   Calculate Conditional Percentile 'IF' in table 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:

=SUMIFS(In[Qty],In[Color],J7)-SUMIFS(Out[Qty],Out[Color],J7)

As the formula is copied down, we get current inventory for each color.

Post navigation

Previous Post:

How to count table columns in Excel

Next Post:

How to calculate two-way lookup VLOOKUP in Excel Table

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Extract word that begins with specific character in Excel
  • How to check if cell contains one of many things in Excel
  • Join cells with comma in Excel
  • How to check if cell contains all of many things in Excel
  • How to count specific words in a range in Excel
  • Convert decimal seconds to Excel time
  • How to calculate nth day of year in Excel
  • Display Date is same month in Excel
  • Get first day of month in Excel
  • Display Days in month in Excel
  • Calculate original loan amount in Excel
  • YIELDMAT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate periods for annuity in Excel
  • How to calculate present value of annuity in excel
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles