Consolidate Rows in Excel Based on Criteria

How-to basics · Updated November 18, 2026

To consolidate rows in Excel based on matching criteria, create a PivotTable: select the data, choose Insert > PivotTable, place the fields to group by in Rows, and place the numeric field in Values, summarized by Sum or another calculation. For a formula-driven summary that updates as source data changes, combine UNIQUE with SUMIFS; use Power Query when you need a repeatable process for larger datasets.

What consolidating rows means

Consolidating rows usually means grouping records that share one or more values, then calculating a summary for each group. For example, a sales list might contain several transactions for the same customer and region. A consolidated report shows one row for each customer-region pair and adds the sales amounts.

This is different from merging cells or deleting duplicate records. Merging cells changes the layout and can make data harder to sort, filter, and analyze. Removing duplicates discards repeated records; it does not add their values together. A proper consolidation keeps the source records intact and creates a separate summary table.

Before starting, decide which columns define a group and what calculation you need. You might group by department and month, then sum expenses; group by product, then count orders; or group by employee, then find the largest payment. The grouping columns are often called keys or criteria, and the column being calculated is the value field.

Choose a method for your task

Excel offers several ways to produce a grouped summary. The right choice depends on whether you need a quick report, live formulas, or a refreshable data-cleaning workflow.

  • PivotTable: Best for quickly exploring and rearranging grouped totals, counts, averages, and other summaries. It does not require writing a formula.
  • UNIQUE and SUMIFS: Useful when the summary should sit in a worksheet grid and recalculate as source values change. These functions are available in Microsoft 365 and Excel 2021 or later.
  • Power Query: Suited to repeatable transformations, especially when you import or combine data and want to refresh the grouped result later.
  • Data > Consolidate: Helpful for combining similarly structured ranges, including some cases where ranges use the same row or column labels. It is less flexible than a PivotTable or Power Query for ongoing analysis.

Whichever method you choose, keep the original data in a rectangular list: one header row, one record per row, and one type of information per column. Avoid blank header cells and subtotal rows inside the source range. Those layout choices help Excel identify the records correctly.

Summarize one criterion with SUMIFS

SUMIFS adds values for records that meet one or more conditions. It is a good option when you already have a list of categories for your report and want a total beside each one. The source records remain unchanged, and the result updates when their values change.

For a one-criterion example, assume regions are in A2:A100, sales amounts are in B2:B100, and the region to summarize is in D2. Enter the following in E2, then copy it down beside the other region names:

=SUMIFS($B$2:$B$100,$A$2:$A$100,D2)

If the rows for North contain sales of 240 and 160, the result beside North is 400. The dollar signs keep the source ranges fixed when you copy the formula, while D2 changes to D3, D4, and so on. SUMIFS returns zero when no records meet the criterion.

The criteria list in column D can be typed manually, or created with a PivotTable. If you want Excel to generate distinct region names automatically, and the region data is in A2:A100, enter this in D2:

=SORT(UNIQUE(A2:A100))

This spills a sorted list of distinct regions into the cells below. Leave those spill cells empty so the results can appear. UNIQUE and SORT require Microsoft 365 or Excel 2021 or later; in older versions, you can make the list with Remove Duplicates on a copy of the source column or use a PivotTable.

Group rows by multiple criteria

For a group defined by more than one field, include a separate criteria-range and criteria pair in SUMIFS. Suppose a sales table has customer names in A2:A100, regions in B2:B100, products in C2:C100, units in D2:D100, and sales amounts in E2:E100. If the summary should show each customer-region combination once, first create the distinct pairs.

With the customer and region fields in A2:B100, enter this formula in G2. It returns two columns of distinct pairs:

=SORT(UNIQUE(A2:B100))

With those spilled pairs in G:H and sales amounts in E2:E100, enter the following in I2 and copy it down for each pair:

=SUMIFS($E$2:$E$100,$A$2:$A$100,G2,$B$2:$B$100,H2)

For example, if Oak Retail has North-region sales of 240 and 160, its North total is 400. A separate South record of 100 appears as a different customer-region group. UNIQUE treats a row as distinct based on the combined values in both columns, while SUMIFS requires both conditions to match.

Use the same pattern for other calculations where appropriate. COUNTIFS counts records that meet multiple conditions, while AVERAGEIFS averages values that meet specified conditions. Check that each criteria range has the same dimensions as the sum or average range; mismatched ranges can produce errors or misleading results.

Create a PivotTable summary

A PivotTable is often the fastest way to consolidate rows because it generates groups from the fields you select. On Excel for Microsoft 365 for Windows, click a cell in the source list and choose Insert > PivotTable. In the dialog, confirm the table or range, choose where the PivotTable should go, and select OK.

  1. In the PivotTable Fields pane, drag each grouping field, such as Customer and Region, into Rows. The order controls the nesting of the groups.
  2. Drag the numeric field, such as Sales, into Values. Excel generally sums numeric fields, but check the displayed calculation.
  3. If needed, open the field menu in Values, choose Value Field Settings, and select Sum, Count, Average, Max, or another available summary.
  4. Use Filters or Columns for fields that should filter or spread the report rather than define its row groups.

PivotTables are interactive: you can change the row order, add another grouping field, or filter categories without rebuilding the source list. They do not necessarily recalculate immediately when you edit the source. Use PivotTable Analyze > Refresh to update the report, or Data > Refresh All to refresh workbook connections and reports.

On Mac, Excel also provides PivotTables, though ribbon labels and the layout of the field pane can vary by release. Excel for the web supports working with PivotTables, but some creation and editing options may differ from the Windows desktop app. If a particular command is missing, open the workbook in desktop Excel.

Use Power Query for repeatable grouping

Power Query is useful when the same cleanup and grouping steps need to be run again on updated source data. It records the transformation steps, so a later refresh can repeat them instead of requiring you to rebuild the summary manually.

For a normal worksheet range in Excel for Microsoft 365 on Windows, first select the data and choose Data > From Table/Range. Confirm that the table has headers if prompted. In the Power Query Editor, select Home > Group By. Choose the column or columns that define a group, select an operation such as Sum, Count Rows, or Average, and choose the column to calculate when the operation requires one. For multiple calculations, use the Advanced option in the Group By dialog to add aggregations.

Choose Home > Close & Load to return the result to Excel. When the source changes, refresh the query using Data > Refresh All. The exact available import and editing options can vary between Windows, Mac, and web versions. Power Query authoring features are not identical across all platforms, so use the Windows desktop app if the instructions or options are unavailable in your build.

Power Query produces a loaded result rather than a cell formula that recalculates after every edit. That distinction matters: change the source, then refresh the query to update the output. This is often an advantage for controlled reporting, because the transformation steps are explicit and can be inspected.

When Data > Consolidate is appropriate

Excel’s Consolidate command is a separate feature from the general idea of grouping duplicate records. On Windows, find it under Data > Consolidate in the Data Tools area. It can combine values from multiple ranges using a function such as Sum or Average, either by matching corresponding positions or by matching labels.

For label-based consolidation, the source ranges need compatible row or column labels. In the dialog, add each reference and select Top row or Left column as appropriate. The command can be useful for combining similarly laid-out department reports, but it is not usually the easiest choice for a changing transaction list with several criteria. PivotTables and Power Query make grouping fields and refresh behavior clearer for that kind of data.

Prepare the data and check the result

Grouping is only as reliable as the source values. Two cells that look identical to a person may not match in Excel: one might contain a trailing space, a different spelling, a number stored as text, or a date that includes a time component. Clean and standardize key columns before interpreting a summary.

  • Use consistent labels, such as North rather than a mix of North, N, and NORTH, unless those values are intentionally separate.
  • Remove unwanted spaces and correct inconsistent spelling. TRIM can help with ordinary extra spaces; imported data may require additional cleanup.
  • Check that amounts are numeric and dates are real Excel dates, not text that merely looks like a date.
  • Keep headers unique and nonblank, and do not place explanatory notes or subtotal rows inside the data range.
  • Compare the grand total in the summary with the source total. If they differ, inspect filters, blank keys, omitted rows, and the selected source range.

Also decide how blanks should behave. A blank customer or region can form its own group in a PivotTable or appear among distinct values from UNIQUE. You may prefer to fill missing labels with a clear value such as Unknown, or exclude incomplete records deliberately. Make that decision explicit so a blank category does not go unnoticed.

Common issues and how to fix them

The formula returns zero: Confirm that the criterion text matches the source and that the amount range contains numbers. A criterion cell with extra spaces or a number stored as text can prevent a match. Check that the criteria ranges and sum range cover corresponding rows.

UNIQUE or SORT shows #NAME?: The Excel version may not support that function. Use a PivotTable or another compatible method, or open the workbook in Microsoft 365 or Excel 2021 and later. Also make sure cells in the spill area are empty; occupied cells can cause a spill error.

A PivotTable total is Count instead of Sum: The source values may contain text or blanks, so Excel may treat the field as nonnumeric. Clean the source values, refresh the PivotTable, and then check Value Field Settings again.

The output misses new records: A formula using fixed ranges such as A2:A100 will not include rows below row 100. Expand the ranges or convert the source into an Excel Table so the data range can grow more naturally. A PivotTable also needs its source range to include new rows; refresh it after updating the source.

Similar labels appear as separate groups: Look for extra spaces, inconsistent punctuation, or different underlying values. A summary does not automatically know that abbreviations or spelling variations represent the same category. Standardize the source first rather than combining groups merely because their labels look close.

Frequently Asked Questions

How do I combine duplicate rows and add their values in Excel?
Use a PivotTable with the identifying fields in Rows and the numeric field in Values, set to Sum. For a formula-based result, create a distinct key list and use SUMIFS to total values for each key.

Can Excel consolidate rows using two or more columns?
Yes. In a PivotTable, put each grouping column in Rows. With formulas, use one criteria-range and criteria pair per column in SUMIFS. UNIQUE can also return distinct combinations from multiple columns in Microsoft 365 and Excel 2021 or later.

Will a consolidated result update automatically?
SUMIFS results recalculate when referenced source cells change. PivotTables and Power Query outputs generally need a refresh after source data changes. A formula also will not include new records outside its referenced ranges unless those ranges expand.

Does Remove Duplicates add the amounts from matching rows?
No. Remove Duplicates deletes repeated records from the selected data; it does not aggregate their values. Use a PivotTable, SUMIFS, or Power Query Group By when you need to retain the combined total.

Can I consolidate rows without a formula?
Yes. A PivotTable can group records and calculate totals, counts, or averages without worksheet formulas. Power Query can also group records through its Group By interface and load the resulting table.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.