Function Problems

Create a Dynamic Customer Product List and Totals in Excel

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

Create a list of unique products and total quantities for a selected customer.

Create a Dynamic Customer Product List and Totals in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the customer

Enter the customer name in E2 exactly as it appears in column A.

2
Enter the formula

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.

3
Test another customer

Change the name in E2 to update the product list and totals. If Excel shows #SPILL!, clear any cells blocking the result area.

4
Accommodate new rows

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.

Use a Dynamic Array Formula to List Products and Totals
No matching customer: FILTER returns a #CALC! error when there are no matching rows. Check the spelling in E2 and confirm that the source contains that customer.
Summarize purchases in WPS Spreadsheets

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. 1. Open the source data: Open your workbook in WPS Spreadsheets and select the purchase data, including its column headers.
  2. 2. Insert a PivotTable: Choose Insert > PivotTable, select a location for the summary, and confirm.
  3. 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. 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.
List products once and sum their quantities by customerFilter the summary without manually copying matching rowsOpen and work with Microsoft Excel XLS and XLSX filesRefresh the PivotTable after source data changes
microsoft office alternative - wps office

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.