To combine like items in Excel, group the repeated item names and total their quantities with a PivotTable or a SUMIF formula. For a quick PivotTable, select the data, choose Insert > PivotTable, then place the item field in Rows and the quantity field in Values. This creates a summary without changing your original list. The steps below cover both methods and explain how to check the results.
What You’ll Need
- An Excel workbook with one row per transaction or record, such as item name and quantity.
- A header row with a clear label for each column, for example Item and Quantity.
- Microsoft Excel for Microsoft 365 on Windows for the ribbon paths below. Excel for Mac has similar PivotTable tools, though pane placement can differ. Excel for the web also supports PivotTables, but some detailed controls may be arranged differently.
This guide treats like items as rows with the same item name whose numeric values should be added together. It does not merge worksheet cells, which is a different operation and can make data harder to sort, filter, and summarize. If your goal is to join text from several cells into one sentence, use a text-joining method instead.
Step-by-Step
- Set up and inspect the source list. Put each record on its own row. Keep item names in one column and the amounts to combine in another. For example, use Item in A1 and Quantity in B1, with records in rows 2 through 8. Avoid blank rows within the list, merged cells, and multiple header rows. Check that quantities are numbers, not text that only looks numeric. A small sample could be: Apples 5, Bananas 3, Apples 4, Oranges 2, Bananas 6, Apples 1, and Oranges 5. The expected totals are Apples 10, Bananas 9, and Oranges 7.
- Turn the source into an Excel Table (recommended). Click any cell in the list and press Ctrl+T on Windows, or Command+T on Mac. Check that the range covers the full list and that My table has headers is selected, then click OK. A Table expands when you add new rows, making it less likely that a later summary will miss newly added records. If you prefer not to use a Table, make sure you select the entire data range in the next step.
- Create a PivotTable. Click inside the Table, then choose Insert > PivotTable. In the dialog, confirm the Table or range and choose New Worksheet so the summary does not overwrite your source. Click OK. In Excel for Mac, use Insert > PivotTable; the dialog and field pane may appear in a different position. In Excel for the web, the command is also on Insert, although available layout controls can vary.
- Place the fields in the right areas. In the PivotTable Fields pane, drag Item to Rows and Quantity to Values. Excel should display one row per distinct item and a total beside each name. If Quantity appears under Rows or Columns, drag it out and put it under Values. For the sample list, the PivotTable should show Apples 10, Bananas 9, and Oranges 7. These numbers come from adding 5+4+1, 3+6, and 2+5 respectively.
- Confirm that Excel is summing, not counting. If the Values area says Count of Quantity, click the field there, choose Value Field Settings, select Sum, and click OK. A count tells you how many records exist for an item; it does not total the quantities. If Sum is unavailable, Excel may be reading the quantity entries as text. Correct the source values, then refresh the PivotTable.
- Use SUMIF when you want a formula-based summary. With item names in A2:A8 and quantities in B2:B8, put a distinct item name, such as Apples, in D2. In E2, enter
=SUMIF($A$2:$A$8,D2,$B$2:$B$8). The result is 10 for Apples. The first range is where Excel looks for a matching name, D2 is the name to match, and the final range contains the values to add. Copy the formula down beside each distinct item. The dollar signs keep the source ranges fixed as the formula is copied. - Optionally generate a distinct item list automatically. With item names in A2:A8, enter
=SORT(UNIQUE(A2:A8))in D2. In Microsoft 365 or Excel 2021 and later, the distinct names spill into the cells below. Then, with the same source layout of item names in A2:A8 and quantities in B2:B8, enter=SUMIF($A$2:$A$8,D2,$B$2:$B$8)in E2 and copy it down alongside the spilled list. This combination updates the list when names are added within the referenced range; for a growing list, use an Excel Table or adjust the references so new rows are included. If your Excel version does not support UNIQUE or SORT, type or copy the distinct names into column D yourself. - Check and label the finished summary. Compare a few totals with the source rows, confirm that every distinct item appears once, and give the result column a useful heading such as Total Quantity. Keep the source data intact. A PivotTable is a report, not a replacement for the original records, and formulas are easier to audit when the output has clear labels.
Common Mistakes
Using Remove Duplicates to add quantities. Data > Remove Duplicates deletes repeated rows; it does not add their quantities. Use a PivotTable or SUMIF when the goal is a total by item. Remove Duplicates is appropriate only when you genuinely want to discard repeated records and have checked which fields define a duplicate.
Combining labels that are not actually identical. Excel may treat Apples, apples, and Apples followed by a space as the same item for many ordinary comparisons, but hidden spaces and spelling variations can still produce confusing groups or different-looking labels. Standardize names before summarizing. Do not combine distinct products just because their names look similar.
Summing the wrong field or counting instead. A PivotTable can default to Count when the selected values contain text or blanks. Check the Values field setting and make sure you selected the numeric quantity column, not the item-name column.
Leaving rows out of the source range. If a manually selected range ends at row 20, records below row 20 are not included. Use a Table where possible, and confirm the source shown in the PivotTable dialog or the formula references.
Expecting a PivotTable to update instantly. A PivotTable may need a refresh after its source changes. A formula summary can also be incomplete if its references do not include newly added rows.
Tips
Use a consistent unit before adding values. Combining 2 boxes and 3 individual items into a total of 5 is misleading unless each record represents the same unit or you convert them first. For money or measurements, check that every row uses the same currency, scale, and meaning.
Keep the raw list separate from the summary. A Table with one record per row is easier to filter, validate, and reuse than a manually grouped list. Add a date or category column if you may later need totals by month or department; a PivotTable can use those fields as filters, columns, or additional row levels.
Choose the method that suits how often the data changes. A PivotTable is convenient for interactive reports and multiple grouping fields. SUMIF is useful when you want totals displayed beside a custom list or included in a worksheet layout. For frequently updated data, base formulas on Table columns or refresh the PivotTable after adding records.
Troubleshooting
My PivotTable shows Count instead of Sum: Check whether the source quantity cells contain text, stray spaces, or nonnumeric symbols. Convert them to numbers, then right-click the PivotTable and choose Refresh. If needed, remove the field from Values and add it again after correcting the source.
One item appears more than once: Look for spelling differences, leading or trailing spaces, punctuation, and inconsistent naming. Correct the source labels consistently and refresh the PivotTable. For formula summaries, make sure the distinct-name list and the names in the source use the same spelling.
A total is too small or missing new records: Check the PivotTable source range or the formula ranges and extend them to cover all rows. If the source is an Excel Table, add new records directly below it and confirm that the Table expanded. Refresh the PivotTable after changes.
The formula returns zero: Confirm that the lookup name in D2 exactly corresponds to an item in column A and that the amounts in column B are numeric. Also check that the formula’s sum range and criteria range start and end on matching rows.
UNIQUE or SORT gives a name error: Those functions require Microsoft 365 or Excel 2021 and later. Enter distinct names manually, or use a PivotTable instead. If a spill formula reports that it cannot expand, clear any existing contents in the cells below its output.
Frequently Asked Questions
Does combining like items delete the original rows?
No. A PivotTable creates a separate summary, and SUMIF displays results in cells you choose. Keep the source list unless you deliberately decide to replace it after checking the summary.
Can I combine like items from more than one worksheet?
Yes, but first bring the records into one consistent list when possible, with the same column headings on each sheet. You can append the rows in a Table and summarize that Table. For separate ranges, Excel’s PivotTable consolidation options and formula setup depend on how the sheets are structured, so combining the source rows is usually simpler to maintain.
Can I total items by name and by month?
Yes. In a PivotTable, put Item in Rows and a date field in Columns, then group dates by month if Excel offers that option for the field. Put Quantity in Values and verify that it uses Sum. Ensure the date column contains real Excel dates rather than text.
Why does my total differ from the number of source records?
A total adds the values in the quantity field, while a record count counts rows. For example, three Apple records with quantities 5, 4, and 1 produce a total of 10 but a count of 3. Check which measure answers your question.
Can I combine item names and add the quantities at the same time?
Yes. Use Item as the grouping field and Quantity as the summed field in a PivotTable, or make a distinct-name list and calculate each total with SUMIF. The item names identify groups; the numeric column supplies the amounts to add.
Related
- Center Text in Excel: Keyboard Shortcut and How to Use It
- Center in Excel Without Merging Cells: A Step-by-Step Guide
- How to Center a Picture in a Cell in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Import Multiple CSV Files into Excel at Once, Step by StepYes. In Excel for Microsoft 365 on Windows, import many CSV files at once by putting them in…
- AG Grid Group Rows: Grouping, Display, and ConfigurationAG Grid group rows are rows the grid creates to represent grouped records; they show a group key,…
- How to Add a New Section to a Word Table of ContentsTo add a new section to a table of contents in Microsoft Word, format the section title with…
- Remove Formatting in Word: Shortcuts and Clear FormattingIn Word for Windows, select the text and press Ctrl+Spacebar to remove direct character formatting, such as bold,…
- How to Add Checkboxes in an Excel File on SharePointTo add checkboxes to an Excel workbook stored on SharePoint, open the workbook in Excel, select the cell…
- How to Lock Specific Cells in Google Sheets for EditingTo lock specific cells in Google Sheets, select the cells, open Data > Protect sheets and ranges, add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.