Excel SUMIF Stock Quantity by SKU: Total Repeated Item Rows

To total stock quantity by SKU when the same item appears on several rows, use =SUMIF(A1:A4,”SKU-104″,B1:B4). In this synthetic export, A1:A4 holds SKUs, B1:B4 holds quantities, and the formula returns 18 for SKU-104.

To total SUMIF stock quantity by SKU when the same item appears on several rows, use =SUMIF(A1:A4,"SKU-104",B1:B4). In this synthetic export, A1:A4 holds SKUs, B1:B4 holds quantities, and the formula returns 18 for SKU-104. Put the formula in an unused result cell, such as D1; it checks the SKU column and adds only the quantities on matching rows.

This is the right pattern for a transaction export, stock count, or adjustment list that repeats an item code. It does not require filtering or manually adding visible rows.

SUMIF stock quantity by SKU: the setup

The first range is the one Excel tests against the SKU criterion. The third argument is the range Excel adds. Keep the two ranges aligned row for row.

CellSKUQuantity
Row 1A1: SKU-104B1: 12
Row 2A2: SKU-220B2: 7
Row 3A3: SKU-104B3: 6
Row 4A4: SKU-315B4: 4

This is synthetic data. SKU-104 occurs twice, so its inventory quantity must include both rows.

How to total repeated SKU rows

  1. Place SKU values in A1:A4 and their numeric quantities in B1:B4.
  2. Select an empty output cell, for example D1.
  3. Enter =SUMIF(A1:A4,"SKU-104",B1:B4) and press Enter.

The expected result in D1 is 18. Excel finds SKU-104 in A1 and A3, then adds the corresponding quantities B1 (12) and B3 (6).

What each argument means

  • A1:A4 is the range: the SKU cells to evaluate.
  • "SKU-104" is the criterion: the SKU to find.
  • B1:B4 is the sum_range: the quantities to add for matching SKU rows.

Microsoft describes the syntax as SUMIF(range, criteria, [sum_range]) and notes that it can evaluate one range while adding values from a corresponding range. See the Microsoft SUMIF function reference for the function syntax and criteria rules.

For a different item, replace only the criterion. For example, =SUMIF(A1:A4,"SKU-220",B1:B4) would total the rows for SKU-220. If you are new to deciding between conditional counting and conditional totals, this SUMIF and COUNTIF guide explains the distinction.

No-match SKU returns 0

A zero result can be useful rather than an error. With the same synthetic data, enter =SUMIF(A1:A4,"SKU-999",B1:B4) in D1. The expected result is 0 because no stock row contains SKU-999.

Before treating 0 as proof that an item has no stock row, verify the exact SKU text. A trailing space, a different prefix, or another source-data variation means the value is not the same SKU criterion. Clean or standardize exported SKU values before relying on the total.

Common stock-total mistakes

Observable symptomCauseConcrete fix
The result is 0 although the item appears in the report.The criterion does not exactly match the stored SKU, often because of extra spaces or a different code.Compare the criterion with the source SKU and remove unwanted spaces before using SUMIF.
The result includes quantities from the wrong rows.The SKU range and quantity range are shifted or do not have the same shape.Use corresponding ranges, such as A1:A4 with B1:B4, not B2:B5.
The formula returns a total for all locations when only one location is needed.SUMIF has only one condition.Use SUMIFS and add a location criteria range and location criterion.

When SUMIFS is the better choice

SUMIF is limited to one test: here, the SKU. If the report later needs quantity for SKU-104 and a particular warehouse, status, or location, use SUMIFS instead. Its argument order differs: SUMIFS(sum_range, criteria_range1, criteria1, ...), so the quantity range comes first. Microsoft documents that distinction and the need for same-sized criteria and sum ranges in its SUMIFS function reference.

For another single-condition reporting application, see how to total unpaid invoice amounts with SUMIF. The layout changes, but the principle is the same: test one column and add the aligned values from another.

Compatibility and limits

Microsoft documents SUMIF for Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, and Excel 2016. If you need a manual fallback in an older workflow, filter the SKU column to the required item and use AutoSum on the visible quantities, then clear the filter before working with the full report again.

This formula does not restrict the total by a second field such as warehouse location or stock status. Use SUMIFS when the total must meet more than one condition. SUMIF also has a documented limitation when matching text strings longer than 255 characters; normal SKU codes are typically far shorter.

Sources

Worked example details and boundary cases

Example inputs

Synthetic stock export: A1 contains SKU-104 and B1 contains 12; A2 contains SKU-220 and B2 contains 7; A3 contains SKU-104 and B3 contains 6; A4 contains SKU-315 and B4 contains 4.

Method

SUMIF evaluates the SKU range A1:A4 for the criterion “SKU-104” and adds the quantities in the corresponding cells of B1:B4. The SKU range and quantity range cover the same four rows.

Expected result

=SUMIF(A1:A4,”SKU-104″,B1:B4) returns 18 because it adds 12 from B1 and 6 from B3. =SUMIF(A1:A4,”SKU-999″,B1:B4) returns 0 because no stock row contains SKU-999.

Limitations

This single-condition formula does not restrict the total by a second field such as warehouse location or stock status. Use SUMIFS when the total must meet more than one condition.

A no-match SKU is a valid boundary: =SUMIF(A1:A4,”SKU-999″,B1:B4) returns 0 because no stock row contains SKU-999.

If the SKU cells contain extra spaces or a different SKU value, they are not an exact match for “SKU-104”; clean the source SKU values before totaling.

The SKU range A1:A4 and quantity range B1:B4 must cover corresponding rows. A shifted or differently sized quantity range can total the wrong quantities.

SUMIF has a documented limitation when matching text strings longer than 255 characters; standard SKU codes avoid that limitation.

Practice with the exact example data

Use a separate blank worksheet for each example. Enter each value at its listed cell address, then type the formula in AA1. Quoted text is shown in JSON notation to preserve spaces: enter the characters inside the quotes, without the quotes, using a leading apostrophe to keep text and spaces literal. An empty quoted string means an explicit empty text value; use an apostrophe entry where appropriate. Compare your result with the expected result below. These are synthetic practice data.

Example 1 — Main case

Cell Input value
A1 “SKU-104”
B1 12
A2 “SKU-220”
B2 7
A3 “SKU-104”
B3 6
A4 “SKU-315”
B4 4

Formula in AA1: =SUMIF(A1:A4,"SKU-104",B1:B4)

Expected result: 18

Example 2 — Boundary case

Cell Input value
A1 “SKU-104”
B1 12
A2 “SKU-220”
B2 7
A3 “SKU-104”
B3 6
A4 “SKU-315”
B4 4

Formula in AA1: =SUMIF(A1:A4,"SKU-999",B1:B4)

Expected result: 0

These examples were checked with a limited calculator supporting numeric aggregates, ASCII text functions, exact criteria and exact first-match XLOOKUP. Only the displayed examples and supported semantics were calculated. They were not executed in Microsoft Excel. This check does not verify every claim in the article.

Synthetic examples are provided for practice. They are not evidence of personal experience, independent human review or execution in Microsoft Excel.

1 thought on “Excel SUMIF Stock Quantity by SKU: Total Repeated Item Rows”

  1. Pingback: SUMIF Sales by Region: Build an Excel Region Summary

Leave a Reply

Your email address will not be published. Required fields are marked *