📌 Stage 2 · Estimating · Job Costing · Estimate Roll-Up · 30–40 min

SUMIF moves the estimate onto the Jobs sheet by itself

Learning goals

  • Aggregate estimate lines onto the Jobs sheet with SUMIF
  • Tie the rolled-up estimate back to the priced contract
  • Spot a thin bid before it becomes a thin job

Concepts

One formula connects two sheets

The Jobs sheet needs each job's total estimated cost, but nobody totals it by hand. Column I reaches into the Estimates journal and adds every line belonging to that job.

This is the pattern the whole workbook repeats: type once in a journal, aggregate everywhere it is needed. The same SUMIF shape will price labor by job in Lesson 8 and billings by job in Lesson 13.

=SUMIF(Estimates!$B$5:$B$1004,$B5,Estimates!$F$5:$F$1004)

Walk the Estimates sheet rows 5 through 1004; wherever the Job # in column B equals this row's job number in B5, add the Est Cost from column F. The kitchen's twelve lines total $77,000.00. Anchors ($) keep the ranges pinned while the criteria cell B5 still slides row by row when the formula is filled down.

Margin, revisited with real totals

With column I live, Lesson 4's margin columns become trustworthy: $96,250.00 - $77,000.00 = $19,250.00 and 20.0% for the kitchen; $3,900.00 and 19.5% for the deck; $9,800.00 and 20.0% for the bath.

Add the won jobs and the company is carrying $132,300.00 of estimated direct cost against $165,250.00 of contracted price. Those two numbers are the denominator pair every later lesson builds on - overhead in Stage 4 and percent complete in Lesson 13 both divide into them.

💡 Tips:

  • Quick pricing check: price = estimate divided by (1 - target margin). At a 20% target, $77,000.00 of estimate needs $96,250.00 of price - exactly what the kitchen carries.
  • If a bid's margin sits below target, fix it here in the estimate, not by hoping the field saves it.

Bid jobs stay visible but outside the totals

The garage bid J-2604 rolls up to $15,500.00 against an $18,600.00 proposed price - 16.7%, thin. It stays on the Jobs sheet with status Bid so the pipeline is visible, but WIP and Job P&L list only won jobs, so Dana's operations numbers never mix with numbers that depend on winning work you have not won.

When the Okafor job signs, the fix is one cell: change Status from Bid to Open and add it to the WIP list - the roll-ups already know its estimate.

Practice

Fixed case (Mar 1 – Jun 30, 2026). Pale-green cells are formulas. Cell I5 on Jobs has been cleared - you will retype it.

  1. Retype the roll-up in I5 — Cell I5 on Jobs is blank. Type =SUMIF(Estimates!$B$5:$B$1004,$B5,Estimates!$F$5:$F$1004) and press Enter. The kitchen estimate returns $77,000.00.
  2. Watch margin update — J5 and K5 react instantly: $19,250.00 and 20.0%. Nothing was typed twice - that is the point of the roll-up.
  3. Compare the three won jobs — I6 should read $16,100.00 (deck) and I7 $39,200.00 (bath). Their margins: 19.5% and 20.0%.
  4. Check the company total — Add I5, I6, and I7 mentally or on a calculator: $77,000.00 + $16,100.00 + $39,200.00 = $132,300.00 of estimated direct cost on won jobs - the check figure the rest of the course leans on.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Typed the SUMIF into I5 and got $77,000.00 for the kitchen estimate
  • ☐ Confirmed the deck rolls to $16,100.00 and the bath to $39,200.00
  • ☐ Verified the won-job estimate total of $132,300.00 and the bid job's separate $15,500.00