Function Problems

How to Track Excel Inventory Movements Across Multiple Sheets

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to manage inventory by entering product barcodes across multiple sheets, retrieving corresponding product information, and combining item quantities without creating duplicate records.

Product: Excel
Device & OS: not provided
Scenario: Tracking inventory movements and consolidating data from multiple transaction sheets into a single master summary sheet.
Observed behavior: The user needs an automated method to look up product details using barcodes and aggregate numerical quantities across varying sheets efficiently.

Before you start

Ensure you have a master reference sheet containing all unique product barcodes with their corresponding details, and maintain consistent column headers across all your movement sheets to simplify formula building.

Solution 1Recommended

Consolidate Inventory Data using XLOOKUP and SUMIF

Use XLOOKUP to automatically pull product details from your master list and SUMIF to add up total quantities from your daily movement sheets.

XLOOKUP is highly efficient for retrieving specific data like product names or categories based on a scanned barcode. When paired with SUMIF, you can calculate total incoming or outgoing quantities across your workbook without generating duplicate rows for repeated items.

Set up unique barcodes

On your summary sheet, paste your list of unique barcodes in the first column (e.g., Column A). Go to the Data tab and select 'Remove Duplicates' to ensure each barcode appears only once.

Retrieve product details

In the adjacent column (e.g., Column B), enter the formula =XLOOKUP(A2, 'Master'!A:A, 'Master'!B:B). This pulls the product name from your Master sheet matching the barcode in cell A2.

Calculate total quantities

In the next column (e.g., Column C), enter the formula =SUMIF('Movements'!A:A, A2, 'Movements'!C:C). This adds together all quantities listed in Column C of the Movements sheet wherever the barcode matches cell A2.

Apply formulas to all rows

Select the cells containing your XLOOKUP and SUMIF formulas, click the fill handle at the bottom right of the selection, and drag it down to apply the formulas to your entire inventory list.

Using Multiple Criteria: If you need to calculate quantities based on multiple conditions (such as specific dates and specific barcodes simultaneously), use the SUMIFS function instead of SUMIF.

Solution 2

Track Barcode Scan Frequency using COUNTIF

If you only need to determine how many times a specific barcode was scanned or moved, use the COUNTIF function.

Efficient Inventory Management

Track Inventory Effortlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful advanced functions like XLOOKUP, SUMIFS, and PivotTables to help you manage complex inventory data across multiple sheets quickly and accurately.

1. Open your inventory workbook: Launch WPS Spreadsheet and open your existing inventory or stock management file.
2. Access the Formulas tab: Navigate to the Formulas tab on the ribbon to easily insert Lookup and Math functions.
3. Use the Function Arguments dialog: Click 'Insert Function' to open the dialog box, allowing you to visually select your multiple sheets and ranges without typing errors.
4. Save your consolidated tracker: Once your tracking formulas are set, save your workbook in the universally compatible .xlsx format.

Fully compatible with Microsoft Excel formulas and standard .xlsx formats.Advanced lookup functions like XLOOKUP and SUMIFS are available natively.Lightweight architecture handles large inventory datasets without lagging.Provides a familiar user interface, eliminating any learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use VLOOKUP instead of XLOOKUP for my inventory tracking?

Yes, VLOOKUP can be used if your barcode column is the leftmost column in your lookup array. However, XLOOKUP is highly recommended because it is more flexible, can search in any direction, and handles missing data natively without requiring an IFERROR wrapper.

How do I calculate quantities from multiple different movement sheets at once?

To calculate totals from multiple transaction sheets, you can combine multiple SUMIF functions in one cell. For example: =SUMIF('Sheet1'!A:A, A2, 'Sheet1'!C:C) + SUMIF('Sheet2'!A:A, A2, 'Sheet2'!C:C).

Why is my SUMIF returning 0 even though the barcodes exist?

This commonly occurs if the barcodes are formatted as text on one sheet and as numbers on another, or if there are trailing spaces. Ensure cell formatting is consistent across sheets, and use the TRIM function if you suspect hidden spaces in your barcode data.

How can I automatically extract a list of unique barcodes from my transaction logs?

You can use the UNIQUE function (e.g., =UNIQUE('Movements'!A:A)) to automatically generate an array of distinct barcodes from your transaction list without manually copying and using the Remove Duplicates tool.

Excel & Data Problems