How to combine duplicate rows and sum

Answer

To combine duplicate rows in Excel and sum their values, follow these steps carefully. This method is effective for datasets where one or more columns have repeating entries, and you want to aggregate numeric values.

Step 1: Identify Duplicate Rows

Duplicate rows are rows that share the same value in one or more columns. For example, if you have a sales table:

Product A appears twice.

Step 2: Use the "Remove Duplicates" Tool (Optional)

This step is optional if you want to remove duplicates without summing.

1. Select the range of your table.

2. Go to Data → Remove Duplicates.

3. Choose the columns to check for duplicates.

4. Click OK.

This will keep one row per unique value and remove others.

Step 3: Combine and Sum Using PivotTable

PivotTables are the most reliable method for combining duplicates and summing values.

1. Select your dataset.

2. Go to Insert → PivotTable.

3. Select where you want the PivotTable (New Worksheet is recommended).

4. Drag the column with duplicates (e.g., Product) to the Rows area.

5. Drag the numeric column you want to sum (e.g., Quantity) to the Values area.

6. Excel will automatically sum duplicates.

Result:

Step 4: Use the SUMIF Formula

For more control, you can use the SUMIF formula to sum values for duplicates without a PivotTable.

Formula: 

=SUMIF(range_to_check, criteria, range_to_sum)

Example: 

=SUMIF(A:A, "A", B:B)

• A:A → Column containing duplicate values

• "A" → Specific value to sum

• B:B → Column containing numeric values to sum

You can drag the formula down for all unique items.

Step 5: Advanced Option – Power Query

Power Query provides a dynamic way to combine duplicates and sum:

1. Select your dataset and go to Data → Get & Transform → From Table/Range.

2. Ensure your data is formatted as a table.

3. In Power Query, select the column with duplicates.

4. Click Group By.

5. Choose Sum for the column to aggregate.

6. Click Close & Load to output the results.

This method works well for large datasets and updates automatically if the source data changes.

Key Notes

• PivotTables are best for quick aggregation.

• SUMIF is better for formula-driven solutions where the result is dynamic in the sheet.

• Power Query is ideal for large or frequently updated datasets.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report