Yes. To combine repeated labels and add their values in Excel, create a PivotTable and put the label field in Rows and the numeric field in Values, summarized by Sum. For a formula-based result, use SUMIF; for repeatable cleanup of larger datasets, use Power Query’s Group By command.
What consolidating rows means
Consolidating rows usually means turning several records with the same identifying value into one summary row, while adding the numbers attached to those records. For example, a sales list might contain several transactions for each product. The consolidated result has one row per product and a total sales amount for each.
This is different from merely combining worksheets or stacking tables. Stacking adds records to a longer list; consolidating groups matching records and calculates a summary. It is also different from removing duplicates: deleting repeated rows would discard transaction detail, while summing preserves the combined value.
Before starting, decide what makes two rows a match. It might be a product name alone, or a combination such as product, region, and month. If you group only by product when the report needs a separate total for each region, Excel will combine values that should remain distinct.
Choose the right method
For most ordinary lists, a PivotTable is the easiest way to summarize data and inspect the result. SUMIF or SUMIFS is useful when you need totals in a fixed report layout or want formulas to recalculate as source values change. Power Query is a strong choice for recurring imports and transformations. The Data Consolidate command is suited to combining similarly arranged ranges, especially across worksheets.
- PivotTable: Best for a quick, interactive summary that can be rearranged by category.
- SUMIF or SUMIFS: Best when a report needs formulas beside a known list of labels or multiple criteria.
- Power Query: Best for repeatable grouping and cleanup of imported or regularly updated data.
- Data Consolidate: Best when combining ranges from different sheets by matching row or column labels, or by matching position.
These tools do not all behave alike. A PivotTable and a Power Query result are summaries built from source data; a formula calculates a result for each criterion; Data Consolidate combines selected references into an output area. Choose based on how the source changes and how much control you need over the resulting layout.
Prepare the source list
Good source data makes every method more reliable. Put one record on each row, use a single header row, and give every column a clear heading such as Product, Region, and Sales. Keep values of the same kind in the same column. Avoid merged cells, blank header names, subtotal rows mixed into the records, and completely blank rows inside the list.
Check the values that will be grouped. Product names such as Pen and Pen followed by an accidental space may be treated as different labels. Spelling variations, inconsistent date formats, and numbers stored as text can also produce unexpected totals. Use consistent names and actual numeric values. If the same item appears under multiple spellings, correct or standardize those labels before summarizing.
For a list that will grow, converting the range to an Excel Table with Insert > Table can make it easier to manage. Tables expand as new rows are added, and PivotTables and formulas can use the table as a recognizable source. A summary still needs to be refreshed or recalculated as appropriate; adding data does not guarantee every existing summary updates automatically.
Summarize repeated rows with a PivotTable
In Excel for Microsoft 365 on Windows, click a cell in the source list and select Insert > PivotTable. Confirm the proposed table or range, choose where to place the PivotTable, and select OK. In the PivotTable Fields pane, drag the field to group by, such as Product, to Rows. Drag the numeric field, such as Sales, to Values.
Excel usually summarizes numeric fields by Sum. If it shows Count instead, the source column may contain text, blanks, or values Excel does not recognize as numbers. Select a value in the PivotTable, open Value Field Settings, and choose Sum if that option is available; if the values are stored as text, correct the source data first. A PivotTable is a summary, not a replacement for the original transaction list.
To group on more than one field, add each field to Rows. For example, put Product above Region to get a product-and-region breakdown. You can also put a field in Columns to display categories across the top. Filters and slicers can help explore a summary without changing the underlying records.
When source data changes, use PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. If you add records outside the original source range, the PivotTable may not include them until its source is expanded. A Table-based source can help with expanding data, but refresh the PivotTable after adding records.
Use SUMIF for a formula-based total
SUMIF adds values whose corresponding cells meet one condition. It works well when you already have a list of unique labels in a report and want a total beside each one. This approach keeps the result in ordinary worksheet cells, which can be convenient for dashboards or fixed templates.
Assume source product names are in A2:A8, their sales amounts are in B2:B8, and the summary product name is in E2. Enter this formula in F2 and copy it down beside the other summary labels:
=SUMIF($A$2:$A$8,E2,$B$2:$B$8)
For this example, the source rows are Pen 12, Notebook 20, Pen 8, Folder 15, Notebook 5, Pen 10, and Folder 5. With Pen in E2, the result is 30, because 12 + 8 + 10 = 30. The dollar signs keep the source ranges fixed when the formula is copied; E2 changes to E3, E4, and so on.
SUMIF uses a range to test, a criterion, and a sum range. The cells in the criteria range and sum range should correspond row by row. If the criterion is typed directly as text rather than referenced from a cell, put it in straight double quotes, as in =SUMIF(A2:A8,"Pen",B2:B8). A cell reference is often easier to maintain because you can change the label without editing the formula.
Use SUMIFS for multiple matching fields
When a total depends on more than one condition, use SUMIFS. For instance, a report might need sales for one product in one region. Put the product criterion in E2 and the region criterion in F2, then calculate the matching total in G2.
Assume product names are in A2:A7, regions are in B2:B7, sales amounts are in C2:C7, the requested product is in E2, and the requested region is in F2. Enter this formula in G2:
=SUMIFS($C$2:$C$7,$A$2:$A$7,E2,$B$2:$B$7,F2)
If the matching Pen and North records contain 12 and 8, the result for Pen in E2 and North in F2 is 20. SUMIFS starts with the sum range, then takes each criteria range and its condition as a pair. All conditions must match for a row to contribute. Use it when one label is not enough to define a group, such as the same product sold in multiple regions.
Combine ranges with Data Consolidate
Excel’s Data Consolidate command can summarize multiple ranges into a destination range. On Windows, select the destination cell, then go to Data > Data Tools > Consolidate. Choose Sum under Function, add each source range with the Reference box and Add button, and decide whether to select Top row or Left column under Use labels in. Choose the label options when the source ranges have matching headings or labels and you want Excel to match those labels rather than relying only on position.
This feature is useful when monthly or departmental sheets have similar layouts. For example, if each sheet has product names down the first column and monthly amounts beside them, adding those ranges and matching the left-column labels can produce a combined total by product. If ranges use different arrangements or inconsistent labels, the result may not match your intended groups. Check the output against a few known values.
Data Consolidate is not the same as a PivotTable and is less flexible for exploring categories afterward. If you select Create links to source data, Excel can create an outlined result linked to the source cells; without that option, the output is a calculated consolidation rather than a live, interactive PivotTable. The exact appearance and available commands can vary by Excel version and platform. On Mac, use the Data tab and look for Consolidate if available in your desktop edition; Excel for the web may not offer the same desktop command set.
Group and sum with Power Query
Power Query is useful when the same consolidation must be repeated, especially when data comes from files or needs cleanup before grouping. In Excel for Microsoft 365 on Windows, select a cell in the source table and choose Data > From Table/Range. In the Power Query Editor, select the column or columns that define a group, then choose Home > Group By.
In the Group By dialog, choose the grouping fields, set a name for the new total column, select Sum as the operation, and select the numeric column to sum. Choose OK to see the grouped result. When it is ready, use Home > Close & Load to return the output to Excel. For a product-and-region summary, select both Product and Region as grouping columns and sum Sales.
Power Query records transformation steps, so the same cleanup and grouping can generally be repeated by refreshing the query after the source changes. It does not automatically update at the instant a source cell is edited; refresh is needed. Power Query authoring features differ across Excel platforms and versions, so desktop Excel is the safer choice when creating a query with detailed transformation steps.
Common problems and checks
A total that is too high or too low often comes from the grouping choice or source range, rather than a faulty sum operation. Before trusting a result, verify that every relevant row is included, that no subtotal rows are being added a second time, and that the grouping fields represent the level of detail you want.
- Excel counts instead of sums: Check whether the values are numeric rather than text, and inspect Value Field Settings in the PivotTable.
- Labels appear more than once: Look for extra spaces, inconsistent spelling, or different date values that only look alike due to formatting.
- New records are missing: Expand the PivotTable source if needed and refresh; check that a query is refreshed after its source changes.
- Totals do not match: Compare a small group manually, check for duplicate transactions, and confirm that all criteria ranges cover the same rows.
- Different categories were combined: Add the missing grouping field, such as Region or Month, rather than grouping on Product alone.
Keep the original records intact until the summary is verified. A useful check is to compare the grand total of the grouped results with the total of the source amount column, provided the source has no excluded rows or non-data entries. If those totals differ, investigate filters, blank or text amounts, and omitted source rows before using the summary.
Frequently Asked Questions
How do I combine duplicate rows and add their values in Excel?
Use a PivotTable with the duplicate identifier in Rows and the values in Values, summarized by Sum. For a formula next to each distinct label, use SUMIF, or SUMIFS when several fields must match.
Can Excel automatically sum rows with the same name?
Yes, but the method determines when the result updates. SUMIF recalculates when worksheet inputs change, while a PivotTable normally needs a refresh after source changes. Power Query also needs a refresh to rerun its grouped transformation.
How do I sum duplicates based on two columns?
Use SUMIFS with one criteria range for each matching column, or create a PivotTable and place both fields in Rows. This keeps, for example, Pen in North separate from Pen in South.
Does Remove Duplicates add values together?
No. Remove Duplicates deletes repeated records based on selected columns; it does not calculate a total for them. Use a PivotTable, SUMIF or SUMIFS, Power Query Group By, or Data Consolidate when the goal is to preserve and add the repeated values.
Which method is best for data from several worksheets?
For separate sheets with the same structure, Data Consolidate can combine labeled ranges, and Power Query can append and group data for a refreshable workflow. If the sheets can first be brought into one clean list, a PivotTable is often the simplest summary.
Related
- Excel Data Validation for an Entire Column, Explained
- Excel Auto Group Rows: How to Group and Ungroup
- Excel Auto Group Rows by Cell Value: What You Can Do
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Expense Tracking Template: Build One That WorksA Google Sheets expense tracking template is a spreadsheet that records each purchase in a transaction log and…
- Google Sheets Dropdown Chips: Setup and UseGoogle Sheets dropdown chips are colored, pill-shaped choices displayed inside cells; create one with Insert > Dropdown or…
- Clear Outlook Roam Cache: Safe Steps and What ChangesTo clear Outlook’s Auto-Complete suggestions, open classic Outlook for Windows and select File > Options > Mail >…
- Google Sheets: Check for Duplicates in a ColumnTo check a Google Sheets column for duplicates, enter a COUNTIF formula beside the first data row: if…
- Freeze Panes Grayed Out in Excel? Causes and FixesIf Freeze Panes is grayed out in Excel for Microsoft 365 on Windows, first press Esc or Enter…
- How to Delete a Stubborn Blank Page in Microsoft WordTo delete a stubborn blank page in Word, show formatting marks with Ctrl+Shift+8 on Windows or Command+8 on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.