How to Combine Two Excel Lists and Sum Matching Values
Question details
The user needs to consolidate products from two separate, dynamic lists into a single unique list and calculate the total combined sales values for each product.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing sales data or product inventories split across two different lists that need to be merged and aggregated dynamically.
- Observed behavior
- The goal is to have a consolidated list displaying each product exactly once alongside its total summed value from both source lists without duplicate entries.
Ensure both of your source lists have consistent spelling for the product names, as minor typos or trailing spaces will cause the formulas to treat them as separate items.
Use INDEX, MATCH, and SUMIF Formulas
This dynamic formula-based approach extracts unique values from two columns and sums their corresponding data automatically as your lists change.
By combining INDEX, MATCH, COUNTIF, and IFERROR, you can create a unique list of items from two separate columns. Once the unique list is created, the SUMIF function easily aggregates the corresponding values.
In a new blank cell (e.g., H3), enter the unique listing formula: =IFERROR(IFERROR(INDEX(B$3:B$20,MATCH(0,INDEX(COUNTIF(H$2:H2,B$3:B$20)+(B$3:B$20=""),),0)),INDEX(E$3:E$20,MATCH(0,INDEX(COUNTIF(H$2:H2,E$3:E$20)+(E$3:E$20=""),),0))),""). Drag the fill handle down to extract the unique products from columns B and E.
In the adjacent cell (e.g., I3), enter the summation formula: =IF(H3="","",SUMIF(B$3:B$20,H3,C$3:C$20)+SUMIF(E$3:E$20,H3,F$3:F$20)). Copy it down the column. This formula adds the values from column C and column F for the specific product identified in cell H3.
Use Data Consolidation or Power Query
An alternative method for merging tables without writing complex array formulas.
Easily Combine Lists and Calculate Totals in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, SUMIF, and Data Consolidation features, making it incredibly simple to combine dynamic lists and sum matching values exactly like Microsoft Excel.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the two lists you want to merge.
- 2. Apply the extraction formula: Enter the nested INDEX and MATCH formula in your destination column to generate the unique list of items.
- 3. Sum the matching values: Use the SUMIF formula to automatically calculate and merge totals across both source tables.
Frequently Asked Questions
Why is my SUMIF formula returning zero?
This usually happens if the text in your criteria cell doesn't exactly match the text in your source list. Check for accidental leading or trailing spaces and ensure the spelling is completely identical.
Can I combine more than two lists using this formula method?
Yes, but the array formula to extract unique items becomes much longer and more complex. For three or more lists, using the Data Consolidate feature, PivotTables, or Power Query is highly recommended.
Will the consolidated list update automatically if I add new data?
Yes, the formula method is dynamic. As long as your new data falls within the referenced ranges (e.g., rows 3 to 20), the unique list and sums will update automatically. Consider using absolute references or named ranges for better scalability.
How do I handle #N/A errors in my unique list?
Wrapping your INDEX and MATCH combination in the IFERROR function, as shown in the recommended formula, will replace these errors with a blank space or a custom message.
