Calculation Issues

How to Calculate Remaining Allocation After Confirmed Requests in Excel

Bushra Parveen Sep 28, 2026 868 views

Question details

The user needs to calculate a running total of remaining allocations by subtracting cumulative confirmed ticket requests for a specific show from the original total.

How to Calculate Remaining Allocation After Confirmed Requests in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing ticket allocations or inventory where multiple requests exist for the same item or show, and remaining stock must update dynamically row by row.
Observed behavior: The user wants the remaining allocation to reflect the original total minus all confirmed requests up to the current row, rather than subtracting only the current row's request from the initial total.

Before you start

Ensure your data is organized in columns with clear headers for the original allocation, the requested amount, the item or show name, and a confirmation status column (e.g., indicating 'y' for confirmed).

Solution 1Recommended

Use SUMIFS to Calculate Cumulative Confirmed Requests

Calculate the running total of remaining allocation using a combination of subtraction and the SUMIFS function to accurately subtract only confirmed requests up to the current row.

To get a running total that resets per item/show and only includes confirmed rows, you must use a mixed cell reference in your SUMIFS formula. This anchors the top of the range while allowing the bottom of the range to expand as you drag the formula down.

Identify your data columns

Determine the column letters for your Original Total (e.g., Column H), Requested Amount (e.g., Column E), Show/Item Name (e.g., Column C), and Confirmed Status (e.g., Column J).

Enter the SUMIFS formula

In the 'Remaining Allocation' column on the first data row (e.g., Row 2), enter the formula: =H2-SUMIFS($E$2:E2, $C$2:C2, C2, $J$2:J2, "y").

Apply the formula to all rows

Press Enter to execute the formula. Then, click the target cell, hover over the bottom-right corner until the fill handle (cross cursor) appears, and drag it down to apply the formula to the remaining rows.

Use SUMIFS to Calculate Cumulative Confirmed Requests

Understanding Mixed References: The notation $E$2:E2 is crucial. The $ signs lock the starting cell ($E$2), so the sum always starts from row 2. The second part (E2) is relative, so it changes to E3, E4, etc., as you drag it down, creating a dynamic running total.

Solution 2

Group and Filter Data Using a Pivot Table

If you do not need a row-by-row running total and only want the final remaining allocation per show, summarize the confirmed requests with a Pivot Table.

Powerful Data Calculation

Easily Calculate Running Totals with WPS Spreadsheet

WPS Spreadsheet fully supports advanced mathematical and logical functions like SUMIFS, making it simple to calculate dynamic inventory or running remaining allocations. Enjoy a familiar interface that handles complex data with ease.

1. Open your workbook in WPS: Launch WPS Office and open your inventory or allocation spreadsheet.
2. Enter the SUMIFS formula: Select the target cell and type your SUMIFS formula to calculate the running total of confirmed requests.
3. Apply to all rows: Hover over the bottom-right corner of the cell until the cross cursor appears, then click and drag down to apply the dynamic formula.
4. Save your progress: Press Ctrl + S to easily save your updated allocation tracker in Excel format.

100% compatible with Microsoft Excel formulas and formats (.xlsx)Free and lightweight spreadsheet softwareSupports advanced data handling functions like SUMIFS, VLOOKUP, and IFBuilt-in robust data analysis and pivot table tools

microsoft office alternative - wps office

Frequently Asked Questions

Why is my running total subtracting unconfirmed requests?

Ensure your SUMIFS formula includes the criteria range for the confirmation column and specifies the correct criteria, such as "y" or "Confirmed". If this condition is omitted, the formula will sum all requests regardless of their approval status.

How do absolute and relative references work in a running total?

A running total requires a mixed range reference like $E$2:E2. The starting cell ($E$2) is locked with dollar signs, meaning it will always start at row 2. The ending cell (E2) is relative. As you copy the formula down, the range expands automatically to include the current row (e.g., $E$2:E3, $E$2:E4).

What should I do if my SUMIFS formula returns an error?

Check that all ranges within the formula are exactly the same size. For example, if your sum range is $E$2:E10, your criteria ranges must also strictly cover rows 2 through 10. Additionally, verify there are no typos in your text criteria.

Excel & Data Problems