Function Problems

How to Combine Bonus Rows by Unique ID in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to consolidate multiple rows labeled as 'Bonus' into a single row for each unique ID within a large Excel dataset.

Product: Excel
Device & OS: not provided
Scenario: Summarizing and aggregating a large dataset where multiple entries exist for the same unique identifier.
Observed behavior: Duplicate IDs with associated bonus values are spread across multiple rows, requiring them to be aggregated into a single summarized row per ID.

Before you start

Ensure your dataset contains clear column headers and check if your version of Excel supports dynamic array functions (Microsoft 365 or Office 2021 and later) before applying these formulas.

Solution 1Recommended

Use Dynamic Array Formulas (UNIQUE, XLOOKUP, and SUMIFS)

This method is highly reliable for most modern versions of Excel, using multiple specialized formulas to extract unique IDs, retrieve associated values, and calculate totals.

By combining the UNIQUE, XLOOKUP, and SUMIFS functions, you can build a dynamic summary table that automatically updates when your source data changes.

Extract Unique IDs

Select an empty cell (e.g., G2) and enter the formula: =UNIQUE(FILTER(C2:C100, C2:C100<>"")). This creates a list of distinct IDs without blank cells.

Retrieve Related Data

In the adjacent cell (e.g., H2), enter the formula: =XLOOKUP(G2#, C2:C100, D2:D100). The G2# references the entire array of unique IDs created in the previous step.

Sum the Bonus Amounts

In the next cell (e.g., I2), enter the formula: =SUMIFS(E2:E100, C2:C100, G2#). This calculates the total bonus amount for each specific unique ID.

Understanding the Spill Operator (#): The hash symbol (#) used in G2# tells Excel to reference the entire spilled array output from the UNIQUE function, rather than just the single cell.

Solution 2

Use the GROUPBY Function

If you are using the latest Microsoft 365 or Office Insider builds, you can use the newly introduced GROUPBY function to achieve this in a single step.

Smart Data Processing

Easily Consolidate Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like UNIQUE and SUMIFS, allowing you to combine rows and summarize data quickly. Experience a lightweight, highly compatible alternative for everyday data processing.

1. Open Your Data: Launch WPS Spreadsheet and open your dataset containing the bonus rows.
2. Extract Unique IDs: Use the =UNIQUE() function in a blank column to pull all distinct identifiers automatically.
3. Calculate Totals: Apply the =SUMIFS() function alongside your unique IDs to aggregate the total bonus amounts seamlessly.
4. Alternative Method: Go to Insert > PivotTable, drag your 'ID' column to Rows and 'Bonus' to Values to combine everything instantly without typing formulas.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Supports modern dynamic array functions like UNIQUE, FILTER, and XLOOKUPBuilt-in PivotTable features for code-free data groupingLightweight installation and fast performance on large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why is the UNIQUE function returning a #NAME? error?

The UNIQUE function is a dynamic array formula available in Microsoft 365, Office 2021, and newer versions of WPS Office. If you see a #NAME? error, your current software version does not support this function. Try using the PivotTable feature instead.

How can I combine duplicate rows without using any formulas?

You can use a PivotTable. Highlight your entire dataset, navigate to the Insert tab, and select PivotTable. Drag the Unique ID column to the 'Rows' area and the Bonus column to the 'Values' area to automatically sum the data by ID.

Can I sum multiple columns using the SUMIFS method?

Yes. If you have additional value columns to sum, you can drag the SUMIFS formula to adjacent columns, ensuring that your sum_range parameter points to the new column you wish to total.

Excel & Data Problems