Create a Dynamic Customer Product List and Totals in Excel
Question details
Create a list of unique products and total quantities for a selected customer.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A customer is selected in E2, and the user wants a summary that avoids manually filtering and copying product purchases.
- Observed behavior
- The desired result is a two-column list showing each matching product once alongside its total quantity.
Check that customers are in column A, products are in column B, and numeric quantities are in column D. Enter the customer name in E2 and leave enough empty cells to the right and below F2 for the results.
Use a Dynamic Array Formula to List Products and Totals
Use this formula when your Excel version supports LET, FILTER, UNIQUE, HSTACK, BYROW, and LAMBDA.
Enter the customer name in E2 exactly as it appears in column A.
Select F2 and enter =LET(p,UNIQUE(FILTER(B2:B14,A2:A14=E2)),HSTACK(p,BYROW(p,LAMBDA(x,SUMIFS(D2:D14,A2:A14,E2,B2:B14,x))))). The results spill into two columns: product and total quantity.
Change the name in E2 to update the product list and totals. If Excel shows #SPILL!, clear any cells blocking the result area.
For a growing source, select the data and choose Insert > Table. If the table is named Table1 and its headers are Customer, Product, and Quantity, replace the formula in F2 with =LET(p,UNIQUE(FILTER(Table1[Product],Table1[Customer]=E2)),HSTACK(p,BYROW(p,LAMBDA(x,SUMIFS(Table1[Quantity],Table1[Customer],E2,Table1[Product],x))))). Adjust the table and header names to match yours.

Summarize Customer Purchases with an Excel PivotTable
Use a PivotTable if you prefer a menu-driven summary or your Excel version does not support the dynamic array formula.
Create Customer Product Totals with a WPS PivotTable
Use a PivotTable in WPS Spreadsheets to filter purchases by customer and sum quantities for each product.
- 1. Open the source data: Open your workbook in WPS Spreadsheets and select the purchase data, including its column headers.
- 2. Insert a PivotTable: Choose Insert > PivotTable, select a location for the summary, and confirm.
- 3. Set up the summary: In the PivotTable field list, place Customer in Filters, Product in Rows, and Quantity in Values. Choose Sum for Quantity if needed.
- 4. Filter and refresh: Select a customer in the PivotTable filter. After changing the source data, right-click the PivotTable and select Refresh; extend the source range first if new rows fall outside it.

Frequently Asked Questions
Why does the formula show a #SPILL! error?
The formula needs empty cells for both output columns and all matching products. Clear any entries blocking the area starting at F2.
Will the formula include purchases added below row 14?
Not with the fixed ranges A2:A14, B2:B14, and D2:D14. Extend those ranges or use Insert > Table and structured references so the source ranges expand with the table.
What happens if the customer in E2 is not found?
FILTER returns a #CALC! error when no rows match. Check the customer name in E2 against the entries in column A.
Why is my PivotTable counting quantities instead of summing them?
In the PivotTable field list, open the quantity field's value settings and select Sum. Also check that the source quantity cells contain numbers rather than text.




