Combine Quantity of the Same Item in Excel

How-to basics · Updated November 20, 2026

To combine quantities for repeated items in Excel, summarize each item with SUMIF or use a PivotTable. For example, with item names in A2:A100, quantities in B2:B100, and a unique item name in D2, enter =SUMIF($A$2:$A$100,D2,$B$2:$B$100) in E2 and copy it down. The result is a total for each item, while the original rows stay intact. This guide covers both methods in Excel for Microsoft 365 on Windows.

What You’ll Need

  • An Excel workbook containing an item-name column and a quantity column. A simple layout is item names in column A and quantities in column B, with headers in row 1.
  • Consistent item labels, such as the same spelling and spacing for each occurrence of an item. If two products have different sizes or versions, include a distinguishing code such as a SKU.
  • A place for your summary, either on the same worksheet or on a new worksheet. Save a copy first if you plan to remove rows or otherwise change the source data.

Step-by-Step

  1. Check and organize the source data. Put one record on each row, with the item name in one column and its quantity in another. For example, A1 might be Item and B1 might be Quantity. Avoid blank rows in the middle of the list, merged cells, or totals mixed in with individual records. Make sure quantities are numbers, not text that only looks like a number. Keep the original records; combining quantities usually means creating a summary, not deleting the rows that explain where each quantity came from.
  2. Create a list of items to summarize. Put a heading such as Item in D1 and Total Quantity in E1. In D2 downward, list each distinct item once. You can type the names manually for a small list, or copy the source item column to a separate area and use Data > Remove Duplicates. Select only the copied list before using that command, and confirm that the item column is selected in the dialog. Remove Duplicates changes the selected cells, so do not run it on your only copy of the source records. If the list is large, a PivotTable can create the distinct item list for you.
  3. Enter the SUMIF formula. With source item names in A2:A100, source quantities in B2:B100, and the first summary item in D2, enter =SUMIF($A$2:$A$100,D2,$B$2:$B$100) in E2. The first range tells Excel where to look for matching names, D2 supplies the name to match, and the last range contains the quantities to add. The dollar signs keep the source ranges fixed when you copy the formula. In this layout, if Apples appears with quantities 3, 2, and 5, the result for Apples is 10.
  4. Copy the formula down. Select E2, then drag the small square at the lower-right corner of the selection down alongside the item list. You can also copy E2, select the cells below it in column E, and press Ctrl+V. Excel changes the D2 reference to D3, D4, and so on, while the dollar signs keep the source ranges unchanged. On a Mac, use Command+C and Command+V to copy and paste; the fill handle works as well. Check that every summary item has a corresponding result.
  5. Review the totals and format them. Compare a few totals with the source rows by filtering the source list for an item and adding its quantities. Check that the summary contains every item and that no item appears twice under slightly different spellings. Select the total cells and use Home > Number to choose an appropriate number format. If quantities should be whole numbers, choose a number format with zero decimal places; this changes the display, not the underlying values.
  6. Use a PivotTable when you want Excel to build the summary. Click a cell inside the source data and select Insert > PivotTable. In the dialog, verify the table or range, choose New Worksheet if you want the summary separate, and select OK. In the PivotTable Fields pane, drag the item-name field to Rows and the quantity field to Values. Excel should show one row per item and a sum of its quantities. If it displays Count instead, continue to the next step and change the calculation.
  7. Set the PivotTable calculation to Sum. In the PivotTable, right-click a number in the Values area and select Summarize Values By > Sum. Alternatively, use the field’s menu in the PivotTable Fields pane, choose Value Field Settings, select Sum, and select OK. If Sum is unavailable or the result is still a count, check whether the source quantities are stored as numbers. After changing source data, right-click the PivotTable and select Refresh. A PivotTable is convenient for grouping and rearranging fields, while the SUMIF summary is a straightforward grid that is easy to reference in other formulas.

Common Mistakes

Names that look alike but are not identical: Excel treats entries such as “Green tea,” “Green tea ” with an extra trailing space, and “Green Tea 250 g” as different labels. Standardize names before summarizing, and decide whether differences in package size, model, or color mean the entries should remain separate. Excel’s standard matching in SUMIF is not case-sensitive, so capitalization alone generally will not create separate groups; meaningful codes or other text differences will.

Numbers stored as text: A quantity imported from another system may be left-aligned or show a warning indicator because Excel reads it as text. SUMIF may not add those values as expected, and a PivotTable may count them instead of summing them. Convert the source quantities to numbers before building the summary. Also check for cells containing spaces, currency symbols typed into the value, or nonnumeric notes such as “few.”

Including a header or the wrong range: The source ranges in the formula should cover the item and quantity records, not unrelated columns. Keep the item range and quantity range the same size and aligned row by row. In a PivotTable, check the selected source range includes both headers and all records, but does not include a manually typed total row that could be counted a second time.

Deleting details instead of summarizing them: Remove Duplicates keeps one instance and discards repeated records in the selected range; it does not add their quantities. Use it only to create a separate list of distinct item names. If the underlying transaction rows need to remain available for auditing, do not replace them with the summary.

Tips

  • Convert the source range to an Excel table by selecting the data and choosing Insert > Table. Tables make it easier to keep headers and records together as the list grows. When new rows are added, refresh a PivotTable; for a formula summary, check that its source ranges include the new records.
  • Use a unique product identifier, such as a SKU, when names are not reliable. Two different products can share a short description, and one product can have several descriptions. Summing on the identifier prevents unrelated items from being combined just because their names look similar.
  • Keep a date, location, or order number in the source data if you may need to break totals down later. A PivotTable can use additional fields in Rows, Columns, or Filters, so you can see quantities by item and by another category without rewriting the source records.
  • Give the summary a clear title and include units in a header, such as Total Quantity (boxes). Quantities measured in different units should not be added together unless you first convert them to a common unit.
  • For a summary that must update when the list of items changes, a PivotTable is often easier to maintain. If you use a hand-built list with SUMIF, remember to add newly introduced items to that list and copy the formula to their rows.

Troubleshooting

The total is zero even though the item is present: Compare the summary label with the source label carefully. Look for leading or trailing spaces, extra punctuation, nonbreaking spaces copied from a website, or a different product code. Re-entering the label from a source cell can help identify a mismatch. Also confirm that the formula’s first range points to the item names, its last range points to the quantities, and both ranges cover the same rows.

The PivotTable shows Count instead of Sum: The quantity column may contain text, blanks, or a mixture of numbers and text. Select the source quantity cells and convert valid entries to numbers, then refresh the PivotTable. If the source contains words or units in the same cells as the quantities, separate the numeric quantity from the descriptive text before summarizing.

Some new records are missing: The SUMIF ranges may stop before the new rows, or the PivotTable may not have been refreshed. Extend the formula ranges to cover all records, or convert the source to a table and refresh the PivotTable after adding data. For large or frequently updated lists, check this whenever you append records.

The formula changes unexpectedly when copied: Verify that the source ranges have dollar signs around both the column letters and row numbers, as in the example. The summary reference should remain relative so it changes from D2 to D3 as the formula is filled down. If Excel displays the formula as text rather than a result, set the cell format to General, edit the cell, and press Enter; also check that the formula does not begin with an apostrophe.

The total seems too high: Look for duplicate source records that represent the same transaction entered twice, or a total row included among the individual records. A summary correctly adds every matching row, including accidental duplicates. Filter the source data for the item and inspect the underlying records before changing the summary.

Frequently Asked Questions

Can I combine quantities without deleting the original rows?
Yes. SUMIF creates totals in separate cells, and a PivotTable creates a separate summary view. Both leave the source records available. Avoid running Remove Duplicates on the original data if you need those repeated rows.

Which is better, SUMIF or a PivotTable?
Use SUMIF for a simple summary beside a fixed list of item names, especially if another worksheet or formula needs to reference the totals. Use a PivotTable when you want Excel to generate groups quickly or rearrange totals by item, date, location, or another field.

Will SUMIF combine names with different capitalization?
Yes. SUMIF matching is not case-sensitive, so “Bolt” and “BOLT” are treated as the same criterion. Extra spaces, different punctuation, or a different SKU can still make entries distinct.

How do I combine quantities for the same item and date?
Add the date as another grouping field. A PivotTable can place Item and Date in Rows, or Item in Rows and Date in Columns. With formulas, use SUMIFS rather than SUMIF when you need to apply multiple conditions, such as matching both an item and a date.

Can I combine items that have different names?
Not reliably without a shared identifier or a mapping that says which names refer to the same product. Standardize descriptions or assign a SKU first. Otherwise, Excel cannot know whether two different labels are spelling variations or genuinely different items.

Why did my PivotTable not update after I changed a quantity?
PivotTables use a stored summary of their source data. Right-click inside the PivotTable and select Refresh after changing the source. If you added rows outside its original source range, expand that range or use an Excel table as the source before refreshing.

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.