Renovation budget by room spreadsheet: SUMIFS per room, with an overrun flag

A room-by-room budget is one table of scope lines with a Room column, plus a summary that uses SUMIFS to total each room. Here is a five-room example with a flag for any room more than 10% over its budget.

General information only, not financial, construction or legal advice. Contingency, holdback and payment-split percentages are your own inputs, and payment and deposit rules vary by contract and by state or province; check yours. Figures cited from other sources are dated where they appear.

One table of lines, one summary by room

A renovation budget by room does not need a sheet per room. It can be a single table with one row per scope line and a Room column, plus a summary that uses SUMIFS to total each room. Adding a sixth room later means adding a name to the room list, not building another sheet. Below is a five-room example, the formulas to rebuild it, and a flag for any room more than 10% over its budget. The 10% is a threshold you type, not a rule.

The lines table

Put the scope lines on one sheet, called Lines here, with headers in row 1 and data from row 2. A layout to rebuild: A Line ID, B Room, C Item, D Category, E Contractor, F Budget, G Base quote, H Approved COs, I Committed, J Paid, K Remaining, L Flag.

I2  Committed   =G2+H2
K2  Remaining   =F2-I2
L2  Flag        =IF(J2>I2,"Overpaid",IF(I2>F2,"Over budget",IF(I2=0,"Not committed","OK")))

Worked example: five rooms

Room-level figures after rolling the lines up. Remaining is Budget minus Committed. Over or under is (Committed - Budget) divided by Budget, rounded to one decimal.

The project as a whole is $550 under budget, and three rooms are over their own. That is why the room view exists: a total can look fine while one room is not. Only the hall bath is flagged, because $1,200 over on $9,000 is 13.3%. The mudroom is $450 over, exactly 10.0%, and the flag asks for more than 10%, so it stays OK. If you want that room flagged, set the threshold to 9% or switch to a greater-than-or-equal test.

Drill into the flagged room

A room flag tells you where to look, not why. Filter the Lines sheet to Hall bath and the cause is on the page:

All four lines are over, and tile alone is half of the $1,200. That points the next conversation at one quote rather than at the whole room.

The summary formulas

On a Summary sheet: rooms in A5 to A9, the threshold in B2 (type 10%), headers in row 4.

B5  Budget       =SUMIFS(Lines!$F$2:$F$200,Lines!$B$2:$B$200,$A5)
C5  Committed    =SUMIFS(Lines!$I$2:$I$200,Lines!$B$2:$B$200,$A5)
D5  Paid         =SUMIFS(Lines!$J$2:$J$200,Lines!$B$2:$B$200,$A5)
E5  Remaining    =B5-C5
F5  Over/under   =IF(B5=0,0,ROUND((C5-B5)/B5,4))
G5  Flag         =IF(F5>$B$2,"Check","OK")

Totals (row 10)  =SUM(B5:B9)   =SUM(C5:C9)   =SUM(D5:D9)
Project over/under  =IF(B10=0,0,ROUND((C10-B10)/B10,4))

Room by category  =SUMIFS(Lines!$F$2:$F$200,Lines!$B$2:$B$200,$A5,Lines!$D$2:$D$200,"Tile")
Lines in the room =COUNTIFS(Lines!$B$2:$B$200,$A5)
Lines over budget =COUNTIFS(Lines!$B$2:$B$200,$A5,Lines!$L$2:$L$200,"Over budget")

Tie-out           =SUM(Lines!$F$2:$F$200)-B10

The ROUND to four decimals (a percentage to two places) stops a value such as 0.1000000001 from tipping a boundary case the wrong way, which matters for a room sitting exactly on the threshold. The ranges run to row 200 so new lines are picked up without editing the formulas.

Checks that catch the usual errors

• The tie-out cell must be 0. If a line's Room is spelled differently from the summary list, such as "Hall Bath " with a trailing space, SUMIFS drops it from every room. The room totals then fall short of the lines total and the tie-out cell shows the difference. A dropdown list for Room prevents the typo.
• Costs that belong to no room. A dumpster or design fee needs a home. Add a room called Whole house to the list, or those dollars vanish from the summary.
• A room with no budget yet. The IF(B5=0,0,...) guard avoids a divide-by-zero error, but it also shows 0.0% for a room that has spending and no Budget. Read that room's Remaining in dollars, which will be negative.
• A line moved between rooms. Change its Room cell and both rooms' totals move at once, while the project total stays the same. If the project total changes, something else was edited.
• Percent versus dollars. A small room crosses 10% on a small dollar amount. Consider also showing Remaining in dollars, as the table does, before reacting.
• The base of the percentage. Divide by the room's Budget, not its Committed. The overrun percentage guide shows why dividing by the larger number understates the overrun.

Where to go next

For the budget-versus-quote side of each line, see committed versus paid. For single-room versions of the same method, see the kitchen and bathroom guides. If the money in a room moves through several trades, the payment schedule guide covers when each payment falls due.

What this does not do

This is a planning tool, not financial, construction or legal advice. It adds up the figures you enter and compares them with the budgets you set; it does not estimate costs or decide whether a room is on track, and the 10% flag is your input. It says nothing about taxes. The Hexloom Renovation Budget Tracker keeps the room list, scope lines and per-room totals in one Excel file.

More renovation budget guides

• Renovation cost overrun percentage calculator: the formula and a worked example
• Renovation contingency tracker: how much contingency is left
• Renovation change order tracker: a spreadsheet for homeowners
• Renovation budget spreadsheet: track committed vs paid
• Kitchen remodel budget spreadsheet in Excel, built line by line
• DIY renovation expense tracker spreadsheet: receipts by room and category
• Contractor payment schedule template for homeowners in Excel
• Contractor bid comparison spreadsheet for homeowners
• Bathroom remodel budget spreadsheet in Excel: how much is really left