How to Track Inventory Purchases and Consumption with SUMIF in WPS Office Spreadsheets
Question details
The user needs to total inventory purchased and consumed quantities by stock code in WPS Office Spreadsheets using SUMIF formulas that update automatically as new records are added.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing an inventory sheet where stock codes are in column B, purchased quantities are in column D, and consumed quantities are in column E, with totals that should change when rows are added.
- Observed behavior
- The desired result is a dynamic total for purchases and consumption for a selected stock code, without manually filtering or summing each time.
Open your inventory workbook in WPS Office Spreadsheets and confirm the stock codes, purchased quantities, and consumed quantities are in consistent columns, such as B, D, and E. Decide which cell will hold the selected stock code if you want dynamic totals.
Use SUMIF Formulas to Total Purchases and Consumption for a Stock Code
Use this solution when you have stock codes in column B, purchased quantities in column D, and consumed quantities in column E, and want totals for a fixed stock code such as 4000.
The SUMIF function checks the stock code range, matches the selected code, and sums the corresponding purchased or consumed quantities. Using full column references like B:B, D:D, and E:E makes the totals update automatically when new rows are added.
Click cell G5, where the total purchased quantity for the selected stock code will appear.
Type =SUMIF(B:B,4000,D:D) in cell G5 and press Enter. This sums all values in column D where column B equals 4000.
Click cell G6 and type =SUMIF(B:B,4000,E:E), then press Enter. This sums all values in column E where column B equals 4000.
Add or change a row with stock code 4000 and confirm that G5 and G6 recalculate automatically.
Use a Cell Reference to Select Stock Codes Dynamically
Use this solution when you want to type any stock code into a selector cell and have the purchase and consumption totals update immediately.
Track Inventory Totals by Stock Code in WPS Office Spreadsheets
Use SUMIF in WPS Office Spreadsheets to total purchased and consumed quantities by stock code, with formulas that update automatically as new inventory records are added.
- 1. Open your workbook: Launch WPS Office Spreadsheets and load the inventory workbook that contains stock codes, purchases, and consumption.
- 2. Enter the purchase total formula: Click the cell for purchase totals and enter =SUMIF(B:B,F2,D:D), then press Enter.
- 3. Enter the consumption total formula: Click the cell for consumption totals and enter =SUMIF(B:B,F2,E:E), then press Enter.
- 4. Type a stock code to update totals: Enter any stock code in the selector cell F2, and the purchase and consumption totals will refresh automatically.
Frequently Asked Questions
What does the SUMIF formula do in an inventory sheet?
It sums the values in one column where a stock code in another column matches the criteria. For example, =SUMIF(B:B,4000,D:D) totals purchase quantities in column D for stock code 4000.
How do I make the stock code change without editing the formula?
Put the stock code in a selector cell such as F2, then use =SUMIF(B:B,F2,D:D). When you change the value in F2, the purchase and consumption totals recalculate.
Will the totals update when I add new inventory rows?
Yes, if the formulas use full column references like B:B, D:D, and E:E. If you use a fixed range, expand the range or switch to full column references so new records are included.
Can I use SUMIF with consumption data in a different column?
Yes. Use the consumption column as the sum range. For example, =SUMIF(B:B,F2,E:E) sums the consumed quantities in column E for the stock code in F2.
Does this SUMIF approach work in both WPS Office Spreadsheets and Microsoft Excel?
Yes. SUMIF formulas are compatible between WPS Office Spreadsheets and Microsoft Excel, and WPS Office Spreadsheets can open and save .xlsx workbooks.
