← Al Davies, 250 High Street

Job costing tracker in SharePoint

Short answer: use two SharePoint lists. A Jobs list has one row per job with the budget. A Costs list has one row per labor or material entry, each pointing to a job. A calculated column flags a cost entry as over a limit, and a saved view shows the jobs to watch. This page does not cover accounting rules. For how costs must be booked, ask your bookkeeper or accountant.

The problem with one big sheet

A single sheet with a column per week gets wide and breaks when two people edit it. Splitting costs into their own rows lets anyone add an entry without touching the job row.

Jobs list

Start the Jobs list as a blank list in Microsoft Lists. Exact names may differ on your screen.

Use Title for the job code, such as J-0417.

Microsoft's formula page says: "To add numbers in two or more columns in a row, use the addition (+) operator or the SUM function." Its example: =SUM([Column1],[Column2],[Column3]). Our advice, not a Microsoft rule: set the returned type of Total budget to Currency or Number when you create the column.

Costs list

Microsoft: "To multiply numbers in two or more columns in a row, use the multiplication (*) operator". Microsoft also says: "Lookup fields are not supported in a formula." So the Line cost formula must use only the plain columns in its own row, never the Job lookup. And: "Calculated fields can only operate on their own row, so you can't reference a value in another row." That is why the sum across a job's rows cannot be a calculated column.

Summing costs per job with a flow

Since a calculated column cannot add up other rows, a flow does it.

  1. Trigger: When an item is created or modified, on the Costs list. Microsoft: "Triggers the flow when you create an item, and each time you modify it in a SharePoint list."
  2. Action: Get items on the Costs list, with a Filter Query on the job. Microsoft supports filters such as "Country/Title eq 'New Zealand'" for a lookup column, where "Title is a column in the referenced list."
  3. Action: add the Line cost values. An Apply to each loop with a running total variable works.
  4. Action: Update item on the Jobs list, set Actual cost to date, and set Over budget to Yes when it passes Total budget.

Limits to know. For Get items, Microsoft says: "The default item limit is 100." You can raise Top Count "up to the list view threshold limit of 5,000." Turn Pagination on. Microsoft also says: "If you go beyond 5,000 item limit, Power Automate fails and generates an error dialog." And the Get items action supports a maximum of 12 lookup columns. See.

Loop guard

The flow in this design edits the Jobs list, not the Costs list that triggers it. That avoids a loop. If you ever make the flow edit the same list that triggers it, add a guard first. See.

A view of jobs going over

  1. On the Jobs list, open the Over budget column heading, filter to Yes, and use Save view as with the name "Over budget".
  2. Sort on Job manager, then Start date. Microsoft says: "you can specify sorting on one or two columns."

Highlight rows in the view. Microsoft's Format a Lists view page says: "To create rules that determine how a row is formatted, select Manage rules in the Conditional formatting box." Pick the Over budget column as the rule condition. See.

Warn at a percentage

Our advice, not a Microsoft rule: add a Warn threshold column, such as 90 percent, and have the flow compare Actual cost to date with Total budget times that threshold. Microsoft shows percent in formulas: =[Column1]*(1+5%). Test the math on a sample job.

Not covered here

This list does not follow any accounting standard. It shows your own entries. Ask your accountant how to match it to your books.

When to get help

You can build all of this yourself in Microsoft 365. If you would rather have it built and tested for your team, I do that. A tracker like this is a fixed price: $5,000 for one, built in 2 weeks. See "What a SharePoint tracker costs to build". Bring one example of the problem to a free call.

Tell me what's still manual

I am Al Davies. I run 250 High Street LLC and build SharePoint trackers and Power Automate flows for operations teams. I work remotely. The first call is free, and I will not try to sell you anything.

See trackers I built: Tools I built

FAQ

What columns does a job cost tracking spreadsheet need?

Job code, budget, then cost entries with type, quantity, unit cost, line cost and date.

Is there a job cost tracker in Excel I can copy?

This page lists the columns in text. In SharePoint you build two lists with no download.

Can a calculated column total all costs for a job?

No. Microsoft says calculated fields only work on their own row. Use a flow to add the rows.

Can SharePoint show jobs that are over budget?

Yes. Set an Over budget Yes/No column with a flow and save a view filtered to Yes.

Should I use SharePoint or Excel for job costing?

Lists give each entry an owner and a flow. See.

Sources