Create a Product-by-City Quantity Summary in Excel Without a PivotTable
Question details
Create a matrix that lists products in rows, cities in columns, and total quantities at each product-and-city intersection without using a PivotTable.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The source data contains Product, City, and Quantity fields, and the user wants a formula-driven summary that updates as the data changes.
- Observed behavior
- The desired result is a dynamic cross-tab report with quantities summed for every matching product and city.
Make sure the Product, City, and Quantity ranges contain the same number of rows and that Quantity values are numeric. If possible, store the data in an Excel Table so the formula can use structured column references.
Build a dynamic product-by-city matrix with LET, UNIQUE, and SUMIFS
Use this method when you want a formula-based summary that generates product rows, city columns, and total quantities automatically.
The formula uses UNIQUE to return distinct products and cities, then uses SUMIFS to calculate each product-and-city total. Define Product, City, and qty as named ranges, or replace them with your own ranges or table columns.
Place the source fields in columns named Product, City, and Quantity. For example, select the data and choose Insert > Table, then use table references such as Table1[Product], Table1[City], and Table1[Quantity].
Click the upper-left cell where the quantity results should begin, leaving enough blank cells to the right and below for the spilled matrix.
Enter =LET(productHeaders,UNIQUE(Product),cityHeaders,TOROW(UNIQUE(City)),SUMIFS(qty,Product,productHeaders,City,cityHeaders)) and press Enter. Replace Product, City, and qty with named ranges or the matching table columns.
Use =UNIQUE(Product) in a separate first column for product labels and =TOROW(UNIQUE(City)) in the header row for city labels, or place labels around the spilled SUMIFS result.
Use PIVOTBY in newer Excel versions
Use PIVOTBY when your Excel version includes this newer dynamic-array function and you want a concise cross-tab formula.
Create a Dynamic Product-by-City Report in WPS Spreadsheets
WPS Spreadsheets can help you turn Product, City, and Quantity data into a formula-driven summary. Use dynamic formulas such as UNIQUE with SUMIFS to generate a report that recalculates when your source values change.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx workbook, and confirm that the Product, City, and Quantity columns are available on the worksheet.
- 2. Create or identify the source ranges: Select each source column and use the Name Box beside the formula bar to create the names Product, City, and qty, or use your existing cell ranges directly in formulas.
- 3. Generate unique row and column values: In an empty area, enter =UNIQUE(Product) for product rows and =TOROW(UNIQUE(City)) for city headers.
- 4. Calculate the matrix totals: In the first quantity intersection cell, enter =SUMIFS(qty,Product,productHeaderCell,City,cityHeaderCell), then fill the formula across and down to calculate every combination.
Frequently Asked Questions
How do I create product and city headers automatically in Excel?
Use =UNIQUE(Product) to create a vertical product list and =TOROW(UNIQUE(City)) to create a horizontal city list. Replace the names with your actual ranges or table columns.
Why does the SUMIFS summary formula return a spill error?
A #SPILL! error means cells in the formula's output area are not empty. Clear or move the blocking values, merged cells, or other formulas, then enter the formula again.
Can I use Excel Table column names instead of named ranges?
Yes. For a table named Table1, use references such as Table1[Product], Table1[City], and Table1[Quantity] in UNIQUE, SUMIFS, LET, or PIVOTBY formulas.
What should I use if my Excel version does not have PIVOTBY?
Use the LET, UNIQUE, and SUMIFS approach, or create a traditional PivotTable from Insert > PivotTable. PIVOTBY is available only in newer Excel versions.
