Use an Excel IF Formula to Deduct Sold Inventory in WPS Office
Question details
The user needs a worksheet formula that calculates remaining inventory by subtracting amounts for rows that have a sale date.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A recreational-vehicle inventory list has total inventory amounts and sale dates, and the user wants remaining stock after sold items are deducted.
- Observed behavior
- The workbook contains inventory amounts and sale dates but does not automatically calculate remaining inventory after sold items are deducted.
Make sure your inventory amounts and sale dates are arranged in separate columns, with blank sale-date cells for unsold items, and note the exact worksheet range before entering the formula.
Use SUM and SUMIFS to Deduct Sold Inventory from Total Inventory
Best for a single total result that automatically subtracts every row whose sale-date cell is not blank.
This approach avoids adding helper columns and reads cleanly: SUM totals all inventory amounts, and SUMIFS subtracts the amounts from rows that have a sale date.
Open the worksheet that contains inventory amounts, for example in B2:B9, and sale dates in C2:C9.
Click the cell where the remaining inventory total should appear, such as E2.
Type =SUM(B2:B9)-SUMIFS(B2:B9,C2:C9,"<>") and press Enter.
Change B2:B9 and C2:C9 to match your actual rows. If you add rows later, select the range and press Ctrl+T to convert it to a table that expands automatically.
Use a Per-Row IF Formula to Show Remaining Inventory Amounts
Use when you want a visible remaining amount on each row before summing the helper column.
Calculate Remaining Inventory with WPS Spreadsheets
Open your Excel workbook in WPS Spreadsheets and use the same SUM, SUMIFS, and IF formulas to deduct sold inventory while keeping the file compatible with Microsoft Excel formats.
- 1. Download and install WPS Office: Download WPS Office from the official WPS website and open WPS Spreadsheets after installation.
- 2. Open your inventory workbook: Click Menu > Open and select the Excel file that contains your inventory amounts and sale dates.
- 3. Enter the remaining-inventory formula: Click the result cell and type =SUM(B2:B9)-SUMIFS(B2:B9,C2:C9,"<>"), then press Enter.
- 4. Save in Excel format: Click Menu > Save As, choose Excel Workbook (*.xlsx), and save the calculated inventory file.
Frequently Asked Questions
Can I use SUMIFS instead of IF to deduct sold inventory?
Yes. Enter =SUM(B2:B9)-SUMIFS(B2:B9,C2:C9,"<>") in the result cell. SUM totals all inventory amounts, and SUMIFS subtracts amounts from rows where the sale-date cell is not blank.
What does the "<>" criteria mean in the SUMIFS formula?
It means not blank. In =SUMIFS(B2:B9,C2:C9,"<>"), WPS Spreadsheets sums only the inventory amounts where the corresponding sale-date cell contains any date or text.
How do I make the formula update automatically when I add new inventory rows?
Select your current data and press Ctrl+T to create a table. Enter the formula with structured table references, or extend the range to include future rows, so added rows are included automatically.
Can WPS Spreadsheets run this Excel IF formula workbook without changes?
Yes. WPS Spreadsheets supports Excel formulas including IF, SUM, and SUMIFS, and it opens and saves .xlsx workbooks so the calculation works without rewriting the formula.
Do I need a formula in every row to deduct sold inventory?
No. A single total formula with SUM and SUMIFS gives the remaining total. Use an IF helper column only when you want a row-by-row remaining amount or visual check.
