Google Sheets Tutorial: Build a Job Tracker That Totals Itself

Updated October 22, 2026 · By Robert Breen

Before you start

You need a free Google account. Type sheets.new into your browser’s address bar to open a new spreadsheet, or open Sheets from the apps menu in Gmail or Drive. Click "Untitled spreadsheet" at the top left and rename it "Jobs, October". Sheets saves as you type.

The data

Steps

1. Type the headers Date, Client, Crew, Status and Amount in A1 to E1, then the eight rows above in rows 2 to 9 (leave Status blank for now if you like).
2. Click row 1, then View > Freeze > 1 row. The header now stays in place when you scroll. This is freezing rows.
3. Select D2:D100 and choose Data > Data validation > Add rule. Under Criteria pick Dropdown and add four options: Booked, Done, Invoiced, Paid. Click Done. Now each Status cell is picked from a list, so nobody types "done " or "Finished". This is data validation.
4. Fill in the Status column from the table above using the drop-downs.
5. In G1 type Status and in H1 Total. In G2 to G5 type Booked, Done, Invoiced and Paid. In H2 type =SUMIF($D$2:$D$100, G2, $E$2:$E$100) and copy it down to H5. Each row now totals the jobs in that status; see SUMIF.
6. Select A2:E100, choose Format > Conditional formatting, pick Custom formula is and enter =$D2="Done". Choose a yellow fill and click Done. Every finished-but-unbilled job is highlighted across the whole row.
7. Choose Data > Filter views > Create new filter view, name it "Crew A" and filter the Crew column to A. Crews can use their own filter view without changing what anyone else sees.
8. Click Share, add your bookkeeper by email as an Editor, and check that general access says Restricted.

Check your work

The totals should read Booked $695, Done $435, Invoiced $620 and Paid $610, which adds up to $2,360. Greene Law (Oct 7) and Rivera Home should be yellow. If a total shows 0, check that the status in column G is spelled exactly like the drop-down option, and that the Amount column holds numbers, not text with a "$" typed in.

Why this beats Friday scrolling

The yellow rows are Maria’s answer: $435 of finished work that has not been invoiced. Each crew lead updates status from their phone, the totals move on their own, and if someone overwrites a row by mistake, version history brings it back.

Check your understanding

Sources

Written from these official help pages. Menus change, so if a button has moved, the help page has the current wording.

• Google Docs Editors Help: Freeze, group, hide or merge rows and columns
• Google Docs Editors Help: Create an in-cell drop-down list
• Google Docs Editors Help: SUMIF
• Google Docs Editors Help: Use conditional formatting rules in Google Sheets
• Google Docs Editors Help: Sort and filter your data
• Google Drive Help: Share and manage access to files and folders

Terms in this lesson

Related in the AI glossary