Financial & Business Formulas
Lookup Hourly Rates & Sum Total Invoice Billing (XLOOKUP & SUMPRODUCT)
Pull role-based hourly billing rates from a master rate card with XLOOKUP and roll up total project invoice value via SUMPRODUCT(Hours, Looked_Up_Rates).
Auditable rate-card invoicing across multi-role consulting timesheets!
Consulting timesheets record Role Code ("ENG-SR", "DESIGN", "ENG-SR", "PM") and Billable Hours in each row while storing hourly dollar rates in a separate Rate Card table. Trying to cram dynamic array lookups directly inside a single opaque formula hides per-line billing errors from client auditors—and fails in engines where SUMIF expects a scalar criterion rather than a range!
Populating an explicit Unit_Rate column via =XLOOKUP(A2, $F$2:$F$4, $G$2:$G$4, 0) and rolling up the grand invoice total with =SUMPRODUCT(B2:B5, C2:C5) gives both line-by-line audit transparency AND single-cell weighted billing! Across 4 timesheet entries (4.0 h ENG-SR @ $180, 2.5 h DESIGN @ $140, 6.0 h ENG-SR @ $180, 3.0 h PM @ $160), SUMPRODUCT totals $720 + $350 + $1,080 + $480 = $2,630.00!
| Timesheet Role Code | Logged Hours (Col B) | XLOOKUP Rate Card ($/hr) | Extended Line Value |
|---|---|---|---|
| ENG-SR (2 Entries: 4.0 + 6.0 h) | 10.0 hours | $180 / hr | $1,800.00 |
| DESIGN (1 Entry: 2.5 h) | 2.5 hours | $140 / hr | $350.00 |
| PM (1 Entry: 3.0 h) | 3.0 hours | $160 / hr | $480.00 (Grand Total = $2,630) |
Total Invoice Billing Across All Timesheet Rows
Multiplies logged hours B2:B5 by XLOOKUP-resolved hourly rates C2:C5 inside SUMPRODUCT.
=ROUND(SUMPRODUCT(B2:B5, C2:C5), 2)Unmapped Role Code Audit Check
Counts timesheet rows with positive hours whose role code failed to match the rate card (Rate = 0).
=COUNTIFS(B2:B5, ">0", C2:C5, 0)Try the example
Use the original values or change them to test your understanding.