Before entering this article, you need to know some basic formulas. Here, if you want to use a Basic Inventory Formula in Excel, what would you do? You are in the right place, we will help you with simple formulas to use a basic inventory formula in Excel 365. Follow the below-given formulas!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Generic Formula
- To use a basic inventory formula, you need to use the below given formula.
=SUMIFS(In[Qty],In[Color],A1)-SUMIFS(Out[Qty],Out[Color],A1)
Syntax Explanation
- SUMIFS – In Excel, this function helps to sum the cells that meet multiple criteria.
- Comma symbol (,) – It is a separator which helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
- Minus Operator (-) – This symbol will help to subtract any two values.
Practical Example
- Firstly, you need to create a sample data with In, Out and Current inventory in Excel.
- To calculate current stock, or inventory, you have to use the formula in formula bar.
=SUMIFS(In[Qty],In[Color],J7)-SUMIFS(Out[Qty],Out[Color],J7)
- Then, you will get the result for the color red as given below in cell K7.
- Now, to calculate the current inventory for the color blue, you have to use the following formula in formula bar.
=SUMIFS(In[Qty],In[Color],J8)-SUMIFS(Out[Qty],Out[Color],J8)
- After that, you will get the result for the color blue as given below in cell K8.
- To calculate the current inventory for the color green, you have to use the following formula in formula bar.
=SUMIFS(In[Qty],In[Color],J9)-SUMIFS(Out[Qty],Out[Color],J9)
- Finally, you will get the result for the color green as given below in cell K9.
Check this too:
- Excel Formulas to calculate Total rows in range
- Formulas to check Multiple cells have the same value case-sensitive!!
A Brief Synopsis
This article gives you complete information about the ** ** simple formulas to use a basic inventory formula in Excel 365. Hope that this article is useful to you. Don’t forget to share your valuable feedback in the below section. Thank you so much for Reading!! Keep learning on Geek Excel!! *And Excel Formulas *!!
Further Reference:
- Kutools For MS Excel & Office 365 Download & Tutorials!
- How to Copy a Cell Formatting from One Cell to Other Cells in Excel 365?
- Excel Formulas to Find the Nth Smallest Value from a Range!!
- Lookup the Next Largest Value in Excel Office 365!!
- Excel Formulas to Find the Last Column Number in a Range ~ Quickly!!
Top comments (0)