How to Consolidate Quantities in Excel by Item Names

How-to basics · Updated November 21, 2026

To combine repeated quantities for each item, put each item name once in a summary list and use SUMIF to add its matching amounts. With item names in A2:A8, quantities in B2:B8, and one summary item in D2, enter =SUMIF($A$2:$A$8,D2,$B$2:$B$8) in E2, then fill it down. For example, if apples appear with quantities 12, 8, and 3, the result for Apples is 23. A PivotTable is a quick alternative, while Excel’s Consolidate command can add similarly arranged ranges.

What You’ll Need

  • An Excel workbook with the item names and quantities you want to combine. The examples below use Microsoft Excel for Microsoft 365 on Windows.
  • A clear heading for each column, such as Item and Quantity. Each source row should represent one item entry.
  • For the formula approach, a place to make a summary list. For a PivotTable, keep the source data in one continuous table with no completely blank rows or columns.
  • If quantities are spread across sheets, note which ranges or tables contain the data. The Consolidate command can combine several ranges, but they should have a compatible layout.

Step-by-Step

  1. Choose the method that fits your data. Use SUMIF when you want a formula-based summary that recalculates as quantities change. Use a PivotTable for a quick report you can rearrange and refresh. Use Data > Consolidate when you need to add corresponding rows or labels from multiple ranges, often on different sheets. The main instructions below show SUMIF first because it makes the matching rule visible and is easy to check.
  2. Check and prepare the source list. Put item names in one column and quantities in another, with a single header row. For example, A1 can contain Item and B1 can contain Quantity. Keep each name and its quantity on the same row. Remove fully blank rows within the list, check that the quantity cells are numeric, and make item names consistent. “Red mug” and “Red mug ” with a trailing space may not match as expected. If different descriptions refer to the same item, decide on one standard description before summing.
  3. Make a list of the items to summarize. In a separate area, type each item name once, with a heading such as Summary item in D1. You can create this list manually for a short catalog. For a longer list in Microsoft 365, you can use UNIQUE, which returns one entry per distinct value and updates when its source changes. With item names in A2:A8, enter =UNIQUE(A2:A8) in D2. If you use this optional formula, leave the cells below D2 empty so the returned list can spill. UNIQUE is available in Microsoft 365 and Excel 2021 or later.
  4. Enter the SUMIF formula beside the first summary item. With source item names in A2:A8, quantities in B2:B8, and the first unique item in D2, enter =SUMIF($A$2:$A$8,D2,$B$2:$B$8) in E2. The first range is the list Excel checks, D2 is the item to match, and the last range contains the amounts to add. In the example data, if A2:A8 contains Apples, Pears, Apples, Pears, Oranges, Apples, Pears, and B2:B8 contains 12, 5, 8, 7, 4, 3, 2, the result for Apples is 23; Pears totals 14 and Oranges totals 4.
  5. Fill the formula down and review the totals. Select E2 and drag the small fill handle at the lower-right corner of the cell down alongside the summary list. The dollar signs keep the source ranges fixed, while D2 changes to D3, D4, and so on. Check a few results by filtering or sorting the source list and adding the matching amounts yourself. Label the total column Quantity total so readers know what the figures represent.
  6. Turn the result into a PivotTable when you want a flexible report. Select a cell in the source data, then choose Insert > PivotTable. Confirm the table or range, choose where to put the PivotTable, and select OK. In the PivotTable Fields pane, drag Item to Rows and Quantity to Values. Excel usually sums numeric quantities automatically. If it shows Count of Quantity instead, open the field’s menu in the Values area, choose Value Field Settings, select Sum, and choose OK. To reflect later edits to the source, right-click the PivotTable and select Refresh. If new rows are added often, format the source list as an Excel table first using Insert > Table; a PivotTable built from a table can include newly added table rows when refreshed.
  7. Use Consolidate for matching ranges across sheets. On Windows desktop Excel, select the cell where the summary should start, then choose Data > Consolidate. In Function, select Sum. In Reference, select a source range and choose Add; repeat for each range. If the ranges have matching labels, select Top row and/or Left column as appropriate so Excel matches entries by their labels. If you leave those options unchecked, Excel combines values by their position, so the ranges must line up exactly. Choose OK to create the summary. This command produces a consolidated result rather than a live, automatically updating report; you may need to run it again after source data changes. On Mac, the command is also under Data, though its ribbon placement can vary. Excel for the web may not offer the same Consolidate command; use SUMIF or a PivotTable there.
  8. Save and document how the totals were made. Keep the original entries intact, and place the consolidated result in a separate area or sheet. Add a short note identifying the source ranges and date of the summary, especially if you used Consolidate. This helps prevent someone from mistaking a one-time total for a report that updates automatically.

Common Mistakes

  • Using Count instead of Sum. A PivotTable may count entries if the quantity cells are stored as text or mixed with nonnumeric values. Convert the source quantities to numbers, then refresh the PivotTable and set the Values calculation to Sum.
  • Expecting similar-looking names to match. Extra spaces, spelling variations, punctuation, and inconsistent item codes can split one item into several summary rows. Standardize the names first. A formula can match text exactly even when the difference is hard to notice on screen.
  • Choosing the wrong Consolidate label settings. Without Top row or Left column, Excel adds values by position, not by recognizing item names. Selecting a label option when the ranges do not have labels in that position can also create an incorrect result.
  • Summing the wrong range. Confirm that the criteria range contains item names and the sum range contains quantities. If the two ranges cover different rows, totals can be incomplete or misleading.
  • Overwriting the source data. Keep the summary separate from the raw list. Editing or replacing source entries to make a total look right makes it harder to trace and verify the result.

Tips

  • Convert a growing source list into an Excel table with Insert > Table. Tables make column headings easier to identify and can expand as you add rows. For formulas, you can also use structured table references, but the cell-range version above is easier to follow when you are starting out.
  • Use one consistent unit for each item. Do not add quantities expressed in different units, such as individual pieces and cartons, unless you first convert them to the same unit.
  • Keep identifiers as well as descriptions when names are not unique. If two products can have the same description, summarize by a unique SKU or item code instead of description alone.
  • Sort the completed summary alphabetically or by total to make it easier to scan. A PivotTable can sort its row labels or values; for a formula summary, select the summary columns together before sorting so names stay paired with their totals.
  • Choose a method based on how the report will be maintained. SUMIF recalculates when its referenced cells change, but a manually typed list of distinct items will not add new names automatically. UNIQUE can supply that list in supported versions. PivotTables need refreshing, and Consolidate generally needs to be run again when sources change.

Troubleshooting

The total is zero. Check that the summary name exactly matches a source entry, and confirm that the formula points to the correct item and quantity columns. Look for leading or trailing spaces and inconsistent spelling. If the amount cells are stored as text, convert them to numbers; a text value that looks numeric may not be included in the sum.

The total is too small or too large. Inspect the range boundaries in the formula, especially the first and last source rows. Make sure the criteria range and sum range cover the same rows. In a PivotTable, confirm that the source includes every row and refresh it. In Consolidate, check that you added every required range and that labels are being matched rather than values being combined by position.

The PivotTable says Count of Quantity. Select a value in that field, open Value Field Settings, and choose Sum. If Sum is unavailable or the result remains a count, some entries may be text, blank, or mixed with error values. Clean the source column so it contains numeric quantities, then refresh the PivotTable.

UNIQUE shows a spill error. Clear any occupied cells in the area where the list needs to spill, including cells with spaces or formulas that display nothing. If you do not have Microsoft 365 or Excel 2021 or later, type or copy a distinct item list manually, or create it with a PivotTable.

Consolidate combines the wrong rows. Undo if the result has just been created and the command is still undoable, then run Consolidate again with the correct label options and source ranges. If the source layouts do not share consistent labels or positions, first combine them into one clean list and summarize that list with SUMIF or a PivotTable.

Frequently Asked Questions

What is the easiest way to consolidate duplicate quantities in Excel?
For a short list, make a distinct item list and use SUMIF beside it. A PivotTable is often quicker when you want Excel to group the entries automatically.

Does Excel have a built-in Consolidate feature?
Yes. In desktop Excel, choose Data > Consolidate, select a function such as Sum, add the source ranges, and set label options if the ranges contain matching headings. It is different from a PivotTable and is not generally a live summary.

Why is my PivotTable counting quantities instead of adding them?
Excel may interpret some or all of the quantity cells as text. Convert them to numeric values, refresh the PivotTable, and select Sum in Value Field Settings.

Will a SUMIF summary update when I add rows?
It updates when values inside its referenced ranges change, but a fixed range such as A2:A8 will not include rows added below row 8. Extend the ranges or use an Excel table-based formula so the source can grow more reliably.

Can I consolidate quantities from multiple worksheets?
Yes. Add each relevant range in Data > Consolidate, or bring the rows into one table and summarize them with a PivotTable or SUMIF. With Consolidate, use matching labels when the sheets are arranged differently; otherwise, positional matching can combine unrelated items.

Can I consolidate different units, such as boxes and individual items?
Not safely by simply adding the numbers. Convert quantities to a common unit first, using a known conversion for each item, or keep the units in separate summary groups.

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.