BuildQuantify logoBuildQuantify
Excel Tutorials / Project Cost Estimator with SUMIF & VLOOKUP
ESTIMATING

Project Cost Estimator with SUMIF & VLOOKUP

12 min read · Template referenced below

Manually retyping unit prices into every estimate invites mistakes and makes it painful to update pricing later. Linking your estimate sheet to a separate price list with VLOOKUP fixes both problems at once.

1
Build a price list tab.
On a separate sheet named "Prices," list every material or labor item with its current unit cost — Item Name in column A, Unit Cost in column B.
2
Pull prices into your estimate automatically.
In your estimate sheet's Unit Cost column, use =VLOOKUP(A2,Prices!A:B,2,FALSE) — this looks up the item name in column A and returns its price from the Prices tab.
3
Calculate line totals.
=Quantity*UnitCost as before, now driven by a price that updates everywhere the moment you change it once on the Prices tab.
4
Roll up totals by trade with SUMIF.
=SUMIF(TradeColumn,"Electrical",LineTotalColumn) gives you a trade-by-trade breakdown for the bid summary page without manual re-entry.
Set the Prices tab up once at the start of a season and just update unit costs there as suppliers change pricing — every past and future estimate referencing VLOOKUP will reflect the update automatically going forward.

This structure turns a one-off estimate into a living pricing system: update a material cost in one place, and every project template that references it recalculates instantly.

Worked example: pulling prices from a Prices tab

Assume the Prices tab contains three items: 3000 psi concrete at $165.00 per cu yd, #4 rebar 20 ft at $11.50 each, and 2x4x8 stud at $3.85 each. In the estimate sheet:

ItemQtyUnit Cost (VLOOKUP)Line Total
3000 psi concrete18$165.00$2,970.00
#4 rebar 20 ft85$11.50$977.50
2x4x8 stud240$3.85$924.00

When your supplier raises concrete to $172.00, change that single cell on the Prices tab. The estimate line becomes $3,096.00 on its own, in every estimate that uses the same Prices tab.

Making the lookup more reliable

  • Always use FALSE (or 0) as the last argument. Omitting it makes VLOOKUP use approximate matching, which can return a wrong price without any warning.
  • Wrap it in IFERROR. =IFERROR(VLOOKUP(A2,Prices!A:B,2,FALSE),"Check item") shows a readable message instead of #N/A when an item is missing or misspelled.
  • Watch for trailing spaces and text-formatted prices. "Concrete " and "Concrete" do not match, and a price stored as text cannot be multiplied.
  • Remember the column order. VLOOKUP can only return values to the right of the lookup column. If your layout needs the opposite, use INDEX/MATCH or XLOOKUP.

Frequently asked questions

Is VLOOKUP obsolete?

XLOOKUP is more flexible in modern Excel, but VLOOKUP is still everywhere and works in all versions. It is perfectly fine for a price list.

How do I keep old estimates from changing when prices change?

Once a bid is submitted, copy the sheet, then paste the Unit Cost column as values (Paste Special → Values) so it no longer depends on the live Prices tab.

Back to all tutorials →
← Back to all tutorials