How to Sum Quantities by Size, Length, and Code in Excel
Question details
The user needs to calculate the total quantities for rows that share the exact same size, length, and code combinations from a dataset.
- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- Consolidating duplicate records into a unique list of items while summing up their corresponding quantities based on multiple matching criteria.
- Observed behavior
- A consolidated list of unique size, length, and code combinations is generated alongside their dynamically calculated total quantities.
Ensure your data has clear column headers and no merged cells. If you plan to use the UNIQUE function, verify that you are using Excel 365, Excel 2021, or WPS Office, as older versions do not support dynamic arrays.
Use UNIQUE and SUMIFS Functions
Extract a unique list of attributes and conditionally sum their quantities using a combination of dynamic arrays and SUMIFS.
This formula approach is highly scalable as more data is added. It uses the UNIQUE function to spill the distinct combinations automatically, followed by SUMIFS to look up and calculate the totals for those matching rows.
Assuming your Size, Length, and Code data are in columns B, C, and D (rows 2 to 440). Select an empty cell, for example, G2, and enter the formula: =UNIQUE($B$2:$D$440). Press Enter to spill the unique list.
Assuming your Quantity is in column A. In cell J2 (beside the first unique combination), type the formula: =SUMIFS($A$2:$A$440,$B$2:$B$440,G2,$C$2:$C$440,H2,$D$2:$D$440,I2).
Format the formula cell as General. Click the fill handle in the bottom right corner of cell J2 and drag it down to fill the formula alongside all spilled unique combinations.
Consolidate Data Using a PivotTable
Consolidate and sum data without writing formulas by utilizing the built-in PivotTable feature.
Sum Data by Multiple Criteria Effortlessly in WPS Office
WPS Spreadsheet fully supports dynamic array functions like UNIQUE and standard functions like SUMIFS, making it easy to summarize large datasets. It also provides a robust PivotTable interface for codeless data consolidation.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your quantity and criteria data.
- 2. Apply the UNIQUE function: Type =UNIQUE(B2:D440) in a blank cell to instantly generate your unique combinations of size, length, and code.
- 3. Calculate totals with SUMIFS: Use the =SUMIFS() function alongside your spilled array to calculate the total quantities based on the unique criteria.
- 4. Alternative: Use PivotTables: Navigate to the Insert tab and click PivotTable to group and sum your multi-criteria data in just a few clicks.
Frequently Asked Questions
Why am I getting a #SPILL! error when using the UNIQUE function?
The #SPILL! error occurs when the dynamic array formula does not have enough blank cells to display all the results. Clear the cells below and to the right of the formula cell to allow the data to spill automatically.
Can I use the UNIQUE function in older versions of Excel?
No, the UNIQUE function is only available in Excel 365, Excel 2021, and modern spreadsheet tools like WPS Office. For older versions, you must use the Advanced Filter feature or create a PivotTable to extract unique values.
How do I automatically include new rows in my SUMIFS calculation?
Convert your source data into a Table by selecting the data and pressing Ctrl+T. Then, update your UNIQUE and SUMIFS formulas to reference the Table columns (e.g., Table1[Size]) instead of fixed ranges like $B$2:$D$440.
