How to Combine and Sum Data by Multiple Criteria in Excel
Question details
The user needs to group data rows by a combination of two categories and sum their corresponding numeric amounts.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a summary report that dynamically totals numerical data based on multiple conditional categories, such as fruit type and sale status.
- Observed behavior
- The goal is to accurately extract unique combinations of multiple criteria from a dataset and output the summed values for each unique pair.
Ensure your dataset has clear row or column headers and contains no merged cells, as merged cells can prevent dynamic array formulas and PivotTables from calculating correctly.
Use Microsoft 365 Dynamic Array Formulas
Leverage the LET, UNIQUE, and SUMIFS functions to automatically generate unique combinations and calculate their totals in a single formula.
If you are using Microsoft 365, dynamic array formulas allow you to perform complex grouping and summing in a single cell. This approach extracts unique pairs from your data arrays and sums the corresponding values seamlessly.
Determine the ranges for your criteria and amounts. For example, assume criteria 1 (fruits) is in B2:K2, criteria 2 (status) is in B3:K3, and the amounts to sum are in B4:K4.
Click on the empty cell where you want the upper-left corner of your summary table to begin.
Type the following formula: =LET(fruit,B2:K2,status,B3:K3,amount,B4:K4,pairs,UNIQUE(HSTACK(TOCOL(fruit),TOCOL(status))),HSTACK(pairs,BYROW(pairs,LAMBDA(r,SUMIFS(amount,fruit,INDEX(r,1,1),status,INDEX(r,1,2)))))).
Press Enter. The formula will automatically spill into adjacent cells, displaying each unique combination of your criteria alongside the calculated total.
Summarize Data Using a PivotTable
Use Excel's built-in PivotTable feature to quickly group and sum data by multiple fields without typing complex formulas.
Easily Combine and Sum Data with WPS Spreadsheet
WPS Spreadsheet provides powerful data analysis tools, including advanced functions and intuitive PivotTables, to help you quickly summarize data by multiple criteria while maintaining full compatibility with Excel files.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing the data you want to summarize.
- 2. Select the data range: Highlight your source data table, ensuring all headers are included in the selection.
- 3. Create a PivotTable: Go to the 'Insert' tab and click 'PivotTable' to open the creation dialog, then click 'OK'.
- 4. Group your criteria: Drag the categories you want to combine into the 'Rows' field and your numeric values into the 'Values' field.
- 5. Save your results: Review your automatically summed combinations and save the document seamlessly in .xlsx format.
Frequently Asked Questions
Can I sum data by multiple criteria in older versions of Excel?
Yes. While the dynamic UNIQUE and LET functions are exclusive to Microsoft 365, you can use the standard SUMIFS function alongside manually created unique lists, or simply insert a PivotTable, which works across all versions of Excel.
Why does my dynamic array formula return a #SPILL! error?
The #SPILL! error occurs when the dynamic array formula does not have enough empty cells to display all its results. To fix this, locate the formula cell and clear any text, numbers, or spaces in the adjacent cells where the data needs to expand.
How can I add a third criterion to my SUMIFS formula?
The SUMIFS function supports multiple conditions. To add a third criterion, simply append another criteria_range and criteria pair to the end of your formula (e.g., SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, criteria_range3, criteria3)).
