Function Problems

How to Calculate Stock Requirements Across Two Excel Locations

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to combine inventory data from duplicate SKUs stored in two separate locations to calculate the total available stock for a requisition worksheet.

Product: Excel
Device & OS: not provided
Scenario: Managing inventory across multiple warehouses or tables and needing a consolidated view of stock levels to determine reorder quantities.
Observed behavior: A formula is required to sum amounts for matching SKUs from two different tables and subtract the required stock level to output the exact order quantity.

Before you start

Ensure your inventory data for both locations is organized into Excel Tables with clear, identically named columns for SKUs and stock amounts.

Solution 1Recommended

Use Combined SUMIF Functions Across Multiple Tables

You can add two SUMIF functions together to calculate the total inventory for a specific SKU located in two different tables.

The SUMIF function is perfect for summing values based on a single condition, such as an SKU number. By adding multiple SUMIF functions together, you can query across different tables or sheets without needing complex array formulas or pivot tables.

Identify the target SKU

Locate the cell containing the SKU you want to calculate the stock for on your requisition worksheet (for example, cell A2).

Enter the first SUMIF formula

Select the target cell for your total stock and type =SUMIF(Table1[SKUs], A2, Table1[Amounts]) to calculate the inventory from the first location.

Add the second location

Append a plus sign and the second function: +SUMIF(Table2[SKUs], A2, Table2[Amounts]). Your complete formula will look like: =SUMIF(Table1[SKUs], A2, Table1[Amounts]) + SUMIF(Table2[SKUs], A2, Table2[Amounts]).

Calculate the quantity to order

To find out how much new stock you need to purchase, simply subtract this total from your required stock level at the end of the formula (e.g., TargetStockCell - (SUMIF1 + SUMIF2)).

Table and Column References: Be sure to replace 'Table1', 'Table2', 'SKUs', and 'Amounts' with the exact names of the tables and columns used in your actual workbook.

Efficient Inventory Management

Calculate and Manage Inventory Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides robust support for advanced formulas like SUMIF, SUMIFS, and dynamic tables, making it easy to track stock across multiple warehouses or retail locations with precision.

1. Open your inventory workbook: Launch WPS Spreadsheet and open the document containing your warehouse location data.
2. Format data as tables: Highlight your inventory lists and press Ctrl+T to convert them into dynamic tables for easier formula referencing.
3. Input the combined SUMIF formula: Click the cell where you need the total stock and enter your combined SUMIF formula.
4. Apply to the whole list: Drag the fill handle in the bottom-right corner of the cell downwards to apply the calculation to all SKUs in your requisition worksheet.

Fully compatible with Microsoft Excel formulas, functions, and structured table references.Lightweight architecture ensures smooth performance even with massive inventory datasets.Built-in inventory management templates to save you setup time.Cross-platform support to check stock levels on Windows, Mac, or mobile devices.

microsoft office alternative - wps office

Frequently Asked Questions

Can I calculate stock requirements for more than two locations?

Yes. You can append additional SUMIF functions to your formula for each new location. For example, add +SUMIF(Table3[SKUs], A2, Table3[Amounts]) to the end of your existing formula.

What if I need to filter inventory by multiple criteria, like SKU and warehouse zone?

If you need to calculate stock based on multiple conditions, use the SUMIFS function instead of SUMIF. The syntax would be =SUMIFS(Table1[Amounts], Table1[SKUs], A2, Table1[Zone], "Zone A").

Why is my SUMIF formula returning zero when there is stock available?

This usually happens if the SKUs in your reference cell and table do not match exactly. Check for trailing spaces, hidden characters, or mismatched data types (e.g., numbers stored as text) between the two locations.

Do I have to use Excel Tables to make this formula work?

No, you can use standard cell ranges like A2:A100 instead of Table1[SKUs]. However, using formatted Tables is highly recommended because the references automatically update when you add new inventory rows.

Excel & Data Problems