Excel基于条件求和（SUMIF）：如何计算表格剩余预算

Hey there! Let's solve this rolling remaining budget calculation for your Excel sheet. Looking at your example, the key is that each row's Remaining value should be the project's total budget minus the sum of all costs for that project up to and including the current row. Here's how to do it:

Step 1: Enter the Formula in the First Remaining Cell #

In cell D2, input this formula:

=B2 - SUMIF($A$2:A2, A2, $C$2:C2)

This computes the initial remaining budget for Project A by subtracting its first cost from the total budget.

Step 2: Fill the Formula Down #

Click on cell D2, hover your cursor over the small square at the bottom-right corner (called the fill handle), then drag it down to your last row (D6 in your example). Excel will automatically adjust the range references for each row as you go.

How the Formula Works #

Let's break down the components to make it clear:

• $A$2:A2 and $C$2:C2: The dollar signs ($) lock the starting row (row 2) in place. As you drag the formula down, the range expands to include all rows from 2 up to the current row (e.g., for D4, it becomes $A$2:A4 and $C$2:C4).
• SUMIF($A$2:A2, A2, $C$2:C2): This function sums up all Costs values where the Project matches the current row's project (e.g., for row 4, it sums all costs for Project A from rows 2 to 4).
• Subtract that cumulative cost sum from the project's Budget to get the remaining budget up to that point.

Verify the Results #

This formula will exactly match the values in your example:

• D2: 110000 - 10000 = 100000 ✔️
• D3: 25000 - 100 = 24900 ✔️
• D4: 110000 - (10000+12000) = 88000 ✔️
• D5: 110000 - (10000+12000+1000) = 87000 ✔️
• D6: 25000 - (100+1000) = 23900 ✔️

Quick Tip #

If your project budgets are consistent across all rows for the same project (like all Project A rows have 110000), this formula works flawlessly. If budgets ever change for a project mid-sheet, we can adjust the formula, but based on your current setup, this should solve your problem.

内容的提问来源于stack exchange，提问作者Robin