Calculate Remaining Stock for Repeated IDs in Excel and WPS Office Spreadsheets
Question details
Calculate remaining stock after each demand entry when the same index appears on multiple rows while correctly handling unique indexes.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A stock ledger contains Index, Stock Qty, and Demand Qty columns. The user needs a formula that subtracts the current and all previous demands for the same index and leaves unique-id rows as opening stock minus current demand.
- Observed behavior
- A simple one-time subtraction from stock quantity over-subtracts or cannot differentiate between repeated ID occurrences, so later rows for the same ID show incorrect remaining stock.
Make sure your data is in a table or a contiguous range with the Index, Stock Qty, and Demand Qty columns in that order, and confirm that transaction rows are ordered by date or entry sequence.
Use LET, COUNTIF, FILTER, and TAKE to create a running demand total
Best for WPS Office and Excel versions that support dynamic array functions; it subtracts the current and all previous demands for the matching index in one formula.
This formula works with an Excel table and structured references such as [@Index]. It builds a running count of the current index, filters all demand quantities for that index, sums only the first n demands with TAKE, and subtracts that sum from the stock quantity.
Click the first data row in the remaining stock column, for example E2.
Type =LET(i,COUNTIF(A$2:A2,[@Index]),f,FILTER([Demand Qty],[Index]=[@Index]),s,SUM(TAKE(f,i)),[@[Stock Qty]]-s).
Press Enter, then select E2 again and drag the fill handle down through the remaining data rows.
Confirm that the first occurrence of each index subtracts only the first demand, while later occurrences subtract all previous and current demands for the same index.
Use a previous-row lookup formula for older spreadsheet versions
Use this when LET, FILTER, or TAKE are not available or when you need a backward-compatible formula for older Excel and WPS Office versions.
Calculate remaining stock for repeated IDs with WPS Office Spreadsheets
Use WPS Office Spreadsheets to open your Excel workbook, apply a running-demand total formula, and get remaining stock values for repeated and unique IDs without losing Microsoft Excel format compatibility.
- 1. Open your Excel workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel file that contains the Index, Stock Qty, and Demand Qty data.
- 2. Prepare the remaining stock column and select the first result cell: Add the header Remaining Stock in column E if needed, then click cell E2.
- 3. Enter the recommended running-demand total formula: Use the LET, COUNTIF, FILTER, and TAKE formula from the solution above, or the previous-row lookup formula if your version requires it.
- 4. Press Enter and copy the formula down: Press Enter, then drag the fill handle from E2 through the last row of your data.
- 5. Review the calculated remaining stock values: Check that unique indexes show opening stock minus the first demand and repeated indexes show the remaining stock decreasing with each subsequent demand.
Frequently Asked Questions
Why does a simple Stock Qty minus Demand Qty formula fail when the same ID appears multiple times?
Because each row subtracts demand from the same opening stock and ignores the demands already consumed in earlier rows for that ID. Use a running demand total or a previous-row lookup formula so each later row starts from the previous remaining stock.
Can I calculate remaining stock if the same index is not in consecutive rows?
Yes. The COUNTIF, FILTER, and LOOKUP methods match the index by value, so they work even when repeated IDs are separated by other entries. Keep the rows in their original transaction order so demand is subtracted sequentially.
What should I do if WPS Spreadsheets does not support LET, FILTER, or TAKE?
Use the previous-row lookup formula =IF(COUNTIF($A$2:A2,A2)=1,B2-C2,INDEX($E$1:E1,LARGE(IF($A$1:A1=A2,ROW($A$1:A1)),1))-C2) and press Ctrl+Shift+Enter if prompted. You can also update WPS Office to a version that supports dynamic array functions.
Do I need to sort the data by Index for the formula to work?
No sorting is required. However, keep rows in the same entry or date order because the formula subtracts demands cumulatively for each ID.
How do I adjust the formula if my data is not formatted as an Excel table?
Replace table structured references with range references: use A2 for [@Index], B2 for [@[Stock Qty]], $A$2:$A$100 for [Index], and $C$2:$C$100 for [Demand Qty].
