Formula Errors

Fix an Excel Formula That Stops Calculating Remaining Stock After a Row

Emma BrownEmma Brown Nov 12, 2026 870 views

Question details

The user needs to fix a remaining-stock formula that calculates correctly through some rows but then returns the initial stock value.

Fix an Excel Formula That Stops Calculating Remaining Stock After a Row
Product
Microsoft Excel
Device & OS
not provided
Scenario
Calculating remaining inventory by subtracting quantities in a Usage Log from the initial stock for each item code.
Observed behavior
After a specific row, the formula no longer subtracts matching usage quantities and instead displays the initial stock amount.
Before you start

Identify the first row where the result changes and compare its item code with the matching codes in the Usage Log. Confirm which columns contain the item code, initial stock, and used quantity before replacing the formula.

Solution 1Recommended

Use a direct SUMIF formula to calculate remaining stock

Use this when each item has an initial stock value and usage entries are recorded on a separate Usage Log sheet or in an Excel Table.

A direct SUMIF formula subtracts the total logged quantity for the current item code from its initial stock. An ISNUMBER wrapper around the entire column is not needed for this calculation.

1
Select the first remaining-stock cell

In the Item Data sheet, click the first cell in the Remaining Stock column, such as the cell on row 3.

2
Enter the worksheet-based formula

Enter =E3-SUMIF('Usage Log'!$C:$C,C3,'Usage Log'!$F:$F), where E3 is Initial Stock, C3 is the current item code, Usage Log column C contains codes, and Usage Log column F contains quantities.

3
Use structured references for Excel Tables

If both datasets are formatted as tables, enter =[@[Initial Stock]]-SUMIF(Table3[Code],[@Code],Table3[Quantity]) in the Remaining Stock column instead.

4
Fill the formula down

Press Enter, then drag or double-click the fill handle in the lower-right corner of the cell to apply the formula to the remaining rows.

Use a direct SUMIF formula to calculate remaining stock
Expected result: Each row should show Initial Stock minus the sum of all Usage Log quantities with the same item code.
WPS Spreadsheets

Calculate Remaining Inventory by Item Code in WPS Spreadsheets

WPS Spreadsheets can calculate remaining stock with a SUMIF formula that totals usage by item code and subtracts it from the initial inventory. Use familiar spreadsheet formulas and table-style references to review inconsistent records quickly.

  1. 1. Open the inventory workbook: Open the .xlsx or .xls file in WPS Spreadsheets and select the sheet containing the remaining-stock calculation.
  2. 2. Select the remaining-stock formula cell: Click the first cell under the Remaining Stock column for the item row you want to calculate.
  3. 3. Enter the SUMIF calculation: Type =E3-SUMIF('Usage Log'!$C:$C,C3,'Usage Log'!$F:$F), adjusting the column references to match your workbook.
  4. 4. Apply the formula to other rows: Press Enter and use the fill handle to copy the formula down the Remaining Stock column.
Use SUMIF to total Usage Log quantities for each item code.Identify unmatched codes, blank values, and extra spaces that prevent matches.Work with Microsoft Excel .xlsx and .xls inventory workbooks.Use a familiar spreadsheet interface for formulas, tables, and fill-down calculations.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my remaining-stock formula show the initial stock instead of a lower amount?

The SUMIF portion is likely returning zero because it cannot find a matching item code, the quantity values are blank or stored as text, or the referenced columns are incorrect. Check the code in the affected row against Usage Log column C.

What formula subtracts all usage entries for the same item code?

Use =E3-SUMIF('Usage Log'!$C:$C,C3,'Usage Log'!$F:$F). It subtracts the total of Usage Log quantities in column F whose code in column C matches C3.

How do I use SUMIF with Excel Tables for stock calculations?

In the table's Remaining Stock column, use =[@[Initial Stock]]-SUMIF(Table3[Code],[@Code],Table3[Quantity]). Replace Table3 and its column names with the names used in your workbook.

Can hidden spaces cause SUMIF not to find an item code?

Yes. SUMIF requires an exact match, so leading or trailing spaces can prevent matches. Use TRIM on the code cells, such as =TRIM(C3), to remove extra spaces.

How can a circular reference affect an inventory formula?

A circular reference occurs when a formula eventually refers back to itself. For example, a Usage Log formula that retrieves a value from Item Data can create a loop if Item Data remaining stock also sums that Usage Log result. Check Excel's Formulas > Error Checking > Circular References list.