How to Calculate Remaining Quantity by Code and Order Number in Excel
Question details
The user needs to calculate the remaining quantity of items required for specific orders by matching both an item code and an order number.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Inventory or order management where produced or completed quantities must be subtracted from total ordered quantities across multiple specific orders.
- Observed behavior
- The user wants a dynamic formula that matches multiple criteria (Code and Order Number) to correctly calculate the outstanding required balance without yielding negative numbers.
Ensure your data is organized into clearly labeled columns, ideally formatted as an Excel Table, with columns for Code, Order Number, Ordered Quantity, and Produced Quantity.
Use SUMIFS to Calculate Remaining Quantity
Utilize the SUMIFS function to sum the ordered quantities and subtract the produced quantities based on multiple criteria (Code and Order Number).
The SUMIFS function allows you to sum values based on multiple conditions. By using two SUMIFS functions—one to find the total ordered and one to find the total produced—you can easily calculate the difference.
Locate the cells containing the specific Code and Order Number you want to calculate the remaining quantity for (e.g., cell A2 for Code, B2 for Order Number).
Enter the formula to get the total ordered quantity: =SUMIFS(Orders[OrderedQty], Orders[Code], A2, Orders[OrderNo], B2). Adjust the table and column names to match your workbook.
Add the subtraction part of the formula using a second SUMIFS: -SUMIFS(Orders[ProducedQty], Orders[Code], A2, Orders[OrderNo], B2).
Your complete formula should look like: =SUMIFS(Orders[OrderedQty], Orders[Code], A2, Orders[OrderNo], B2) - SUMIFS(Orders[ProducedQty], Orders[Code], A2, Orders[OrderNo], B2). Press Enter to view the result.
Calculate Using Standard Cell Ranges
If your data is not formatted as an Excel Table, use absolute cell references within the SUMIFS function.
Calculate Remaining Quantities Quickly in WPS Spreadsheet
WPS Spreadsheet fully supports SUMIFS, MAX, and advanced table features, allowing you to manage complex inventory and order calculations with ease.
- 1. Open your workbook: Launch WPS Spreadsheet and open your inventory or order tracking document.
- 2. Select the calculation cell: Click on the cell where you want the remaining quantity to appear.
- 3. Input the formula: Type your =SUMIFS() formula to subtract the produced items from the ordered items based on your code and order number.
- 4. Fill the column: Press Enter to calculate the result, then drag the fill handle down to apply it to your entire list.
Frequently Asked Questions
Why is my SUMIFS formula returning a #VALUE! error?
This usually happens if the range sizes in your formula do not match. Ensure that the sum_range and criteria_ranges all cover the exact same number of rows and columns.
Can I add a third condition, like a Date or Customer Name?
Yes, SUMIFS can handle up to 127 criteria pairs. Simply add another comma after your last criteria, select the new criteria range, add another comma, and specify the new condition.
What if my 'Produced Quantity' cell is blank instead of zero?
SUMIFS treats blank cells as zero when summing. Your formula will still calculate correctly, and the remaining quantity will simply equal the total ordered quantity.
