Bill of Materials (BOM) Cost Roll-Up Formula
Roll up a multi-level bill of materials in Excel: extended component cost with scrap, sub-assembly totals and finished-good unit cost. Free BOM template example.
=LET( parent, [@AssemblyID], qty, BOM_Lines[QtyPer], scrap, BOM_Lines[ScrapPct], cost, BOM_Lines[UnitCost], SUM(FILTER(qty * (1 + scrap) * cost, BOM_Lines[ParentID] = parent, 0)) )
The assembly or finished good whose cost is being rolled up.
Bill of Materials (BOM) Excel Template & Unit Cost Roll-Up
Skip writing this formula by hand — download the pre-built Bill of Materials (BOM) Excel Template & Unit Cost Roll-Up workbook with live structured tables, KPI dashboard, and sample data.
The Problem with Standard Excel Approaches
A flat BOM list hides the real unit cost: scrap allowances, purchase-unit conversions and nested sub-assemblies are usually added by hand, so quoted prices drift away from actual material cost.
How Modern Dynamic Arrays Execute This Formula
Lists every component once with its parent ID, quantity per and scrap %.
Calculates extended cost per line: quantity per × (1 + scrap) × unit cost.
Sums the lines of each parent, so sub-assembly costs feed the next level up.
Adds labor and overhead per assembly to reach a fully loaded unit cost.
Common Excel Errors & How to Fix Them
Solution: The third FILTER argument (0) returns zero instead; check the parent ID spelling.
Solution: Store a purchase-to-usage conversion factor and divide unit cost by it.
Skip building this from scratch.
This formula is already fully wired, error-checked, and formatted with Big-4 print-ready A4 reports inside Bill of Materials (BOM) Excel Template & Unit Cost Roll-Up.