
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.
| Cell | SKU | Quantity |
|---|---|---|
| Row 1 | A1: SKU-104 | B1: 12 |
| Row 2 | A2: SKU-220 | B2: 7 |
| Row 3 | A3: SKU-104 | B3: 6 |
| Row 4 | A4: SKU-315 | B4: 4 |
This is synthetic data. SKU-104 occurs twice, so its inventory quantity must include both rows.

How to total repeated SKU rows
- Place SKU values in
A1:A4and their numeric quantities inB1:B4. - Select an empty output cell, for example
D1. - 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:A4is the range: the SKU cells to evaluate."SKU-104"is the criterion: the SKU to find.B1:B4is 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 symptom | Cause | Concrete 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
- Microsoft Support: SUMIF function syntax, matching criteria, and corresponding sum ranges
- Microsoft Support: SUMIFS for totals with more than one condition
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.
Pingback: SUMIF Sales by Region: Build an Excel Region Summary