Function Problems

How to Create Invoices and Packing Lists for Multiple Containers

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to generate a single commercial invoice and packing list per shipment when the shipment involves multiple containers and various SKUs.

Product: Spreadsheet
Device & OS: not provided
Scenario: Generating commercial invoices and packing lists for logistics shipments with multiple containers and SKUs using automated formulas.
Observed behavior: The user needs a method to dynamically retrieve a list of all containers and seal numbers for a specific invoice, and accurately calculate the total quantity of each SKU across those containers.

Before you start

Ensure your master data sheet is organized with clear columns for Invoice Numbers, Container/Seal Numbers, SKUs, and Quantities, and verify that your spreadsheet software supports dynamic array formulas like FILTER.

Solution 1Recommended

Use FILTER and SUMIFS Formulas to Automate Shipments

Extract dynamic lists of container numbers using the FILTER function, and aggregate total SKU quantities across all containers using the SUMIFS function.

By combining dynamic array functions and conditional sum formulas, you can automate your logistics paperwork without manually copying and pasting data. The FILTER function easily retrieves multiple container numbers for one invoice, while SUMIFS accurately totals the SKUs.

Extract Container Numbers with FILTER

In your packing list sheet, select the first output cell for the container list. Enter the formula: =FILTER('XYZ-summary'!S:S,'XYZ-summary'!B:B='Invoice_XYZ#24'!H3). Ensure there is no data in the cells immediately below this formula so the results can spill down automatically.

Set Up the Invoice Reference

Make sure your reference cell (e.g., H3) contains the exact invoice number you want to generate the paperwork for. When you update this cell to a new invoice number, the FILTER formula will automatically refresh the container list.

Calculate Total SKU Quantities with SUMIFS

In your commercial invoice sheet, select the quantity cell next to your first SKU. Enter the formula: =SUMIFS(QtyRange,InvoiceRange,$H$3,SKURange,A2). Replace the named ranges with your actual columns. Press Enter, then drag the fill handle down to calculate totals for the rest of your SKUs.

Formula Efficiency Tip: Using absolute cell references (like $H$3 for the invoice number) ensures your formula remains targeted on the correct invoice when you copy the SUMIFS formula down to other SKU rows.

Powerful Spreadsheet Automation

Easily Manage Invoices and Packing Lists with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER and multi-condition aggregation like SUMIFS, making it effortless to build automated invoice and packing list templates for your business.

1. Open WPS Spreadsheet: Launch WPS Office and open your logistics summary workbook containing the shipment data.
2. Apply the FILTER Function: Navigate to your packing list tab and use the FILTER function to automatically list all containers associated with your target invoice number.
3. Summarize SKUs with SUMIFS: Use the SUMIFS function on your commercial invoice tab to accurately total the quantities for each SKU across the multiple containers.
4. Save and Export: Save your updated workbook or export the generated invoices directly to PDF for easy sharing with clients and freight forwarders.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports dynamic arrays to seamlessly manage multi-container shipment lists.Includes built-in financial and logical functions for accurate logistics calculations.Free and lightweight alternative with a highly intuitive user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my FILTER formula returning a #SPILL! error?

A #SPILL! error occurs when the dynamic array formula needs to display multiple results, but the required output cells below the formula are blocked by existing data. Clear any text, numbers, or spaces below your formula cell to allow the results to populate correctly.

Can I use a PivotTable instead of SUMIFS for the commercial invoice?

Yes, you can insert a PivotTable based on your master summary data. Drag the 'Invoice' field to the Filters area, 'SKU' to the Rows area, and 'Quantity' to the Values area. This allows you to quickly aggregate SKU totals per shipment without writing manual formulas.

What if my spreadsheet software doesn't support the FILTER function?

If you are using an older version of a spreadsheet program that lacks dynamic array support, you can either use a complex combination of INDEX and AGGREGATE functions to extract the list, or easily upgrade to a modern suite like WPS Office, which fully supports dynamic array formulas for free.

Excel & Data Problems