How to Calculate Remaining Inventory using SUMIFS and SUMPRODUCT in Excel
Question details
The user needs to create an inventory summary matching stock items to calculate remaining quantities by adding ordered, received, and produced amounts, and subtracting used amounts.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating an inventory management summary from multiple data tables (receiving, ordering, and shop-floor usage).
- Observed behavior
- Needs to combine multiple criteria formulas (like SUMIFS, SUMPRODUCT) to calculate the exact stock balance and hide cells displaying zero values.
Ensure that your source data tables for ordering, receiving, and shop-floor usage have consistent spelling for stock item names, and that your numerical quantity columns are formatted as numbers.
Calculate Remaining Stock using SUMIFS and SUMPRODUCT
Combine SUMIFS or SUMPRODUCT functions to dynamically add incoming stock and subtract outgoing usage based on specific criteria like Stock Pack and Bar/Tube.
To calculate the exact remaining stock, you need to aggregate data across multiple tables. The SUMPRODUCT and SUMIFS functions are ideal for summing values that meet multiple criteria.
Instead of using VLOOKUP, which only returns the first matched record, SUMIFS will add together all matching transaction records to give an accurate total.
Use the SUMPRODUCT or SUMIFS formula to total the ordered, received, and produced quantities from your receiving and ordering table by referencing criteria like Stock Pack and Bar/Tube.
Use a separate SUMIFS formula to total the used quantities from your shop-floor data table based on the same stock criteria.
Subtract the total used quantities from the total incoming stock by combining the formulas. For example: =SUMIFS(Incoming_Range, Criteria_Range, Criteria) - SUMIFS(Used_Range, Criteria_Range, Criteria).
Hide Zero Balances in Excel Summaries
Adjust your Excel application settings to display a blank cell instead of a 0 when a stock item's balance reaches zero.
Use WPS Spreadsheet for Advanced Inventory Formulas
WPS Office Spreadsheet fully supports complex array formulas, SUMIFS, SUMPRODUCT, and advanced data formatting, allowing you to build robust inventory management systems with ease.
- 1. Open your inventory workbook: Launch WPS Spreadsheet and open your existing Excel inventory data file.
- 2. Apply the SUMIFS function: Use the SUMIFS formula to total your received and produced quantities based on specific stock items.
- 3. Calculate remaining stock: Subtract your shop-floor usage data from the total incoming stock using combined formulas in your summary cells.
- 4. Hide zero balances: Navigate to Menu > Options > View and uncheck 'Zero values' to keep your summary sheet clean and easy to read.
Frequently Asked Questions
Can I use VLOOKUP instead of SUMIFS for inventory summaries?
VLOOKUP only returns the first matching value it finds. If you have multiple transactions for the same stock item, VLOOKUP will ignore the rest. You must use SUMIFS, SUMIF, or SUMPRODUCT to add all corresponding transactional values together.
What is the difference between SUMIF and SUMIFS?
SUMIF is used when you only have one condition to match (e.g., adding stock for a single item type). SUMIFS allows you to check multiple criteria simultaneously, such as matching both 'Stock Pack' and 'Bar/Tube' categories.
Why is my SUMIFS formula returning a #VALUE! error?
Common reasons include mismatched range sizes between the sum_range and criteria_range. Ensure all column ranges referenced in the SUMIFS or SUMPRODUCT formula are exactly the same size (e.g., all spanning rows 2 to 100).
Is there a formula-based way to hide zero balances without changing Excel options?
Yes. Instead of changing application settings, you can format the cells using a custom number format like '0;-0;;@', or use an IF statement: =IF(YourFormula=0, "", YourFormula).
