Why this matters
Maria (made up) runs a six-person cleaning company. Jobs get finished every day, but invoices go out late because nobody can see which jobs are done and not yet billed. Every Friday she scrolls through texts and a paper list to work it out, and some jobs slip through.
A shared Google Sheet fixes that for free. In this module you build her job tracker: Status is picked from a list so nobody types "done " or "Finished", each status totals itself with SUMIF, finished-but-unbilled jobs turn yellow, and each crew gets its own filter view.
What you’ll build
A Google Sheet called "Jobs, October" with eight jobs, a frozen header, a Status drop-down (Booked, Done, Invoiced, Paid), SUMIF totals for each status, a yellow highlight on every job that is done but not invoiced, and a saved "Crew A" filter view.
With the sample jobs the totals read Booked $695, Done $435, Invoiced $620 and Paid $610. The $435 is Maria’s answer: finished work nobody has billed yet.
What you need
- Nothing to start the practiceThe practice is built from real Google Sheets screens and does not touch any account.
- A free Google account, to do it for realType sheets.new in your browser’s address bar to open a blank spreadsheet.
Concepts
3 ideas you’ll use in the practice.
A drop-down keeps the data clean
Data › Data validation › Add rule turns a range into a drop-down list. The boxes say "Option 1" and "Option 2" as real text, so select that text before you type, or you get "Option 1Booked".
Formulas and colour rules only match exact text. Picking from a list means "Done" is always spelled the same way, so the totals never miss a row.
SUMIF totals one status at a time
SUMIF adds the numbers in one column where another column matches a value. =SUMIF($D$2:$D$100, G2, $E$2:$E$100) means: look down Status, and where it equals the label in G2, add up the Amount.
The dollar signs lock the ranges, so you can copy the formula down to H5 and only G2 changes to G3, G4 and G5.
A filter view changes only your view
A normal filter hides rows for everyone who has the sheet open. A filter view (Data › Create filter view) is a saved way of looking at the sheet that only changes what you see, so each crew lead can keep their own.
Share the sheet with named people and keep General access on Restricted. "Anyone with the link" opens it to anyone who gets the link.
Practice
Do every step yourself in a practice copy of the real tool. It checks your work, and “Do it for me” finishes any step you get stuck on.
Do it for real
Now repeat it in your own account. Tick each step as you go.
Quick check
3 questions to check you’ve got it.
1. What does the Status drop-down prevent?
- Sharing the file
- Typos like "done " or "Finished" that break the totals
- Sorting the jobs
Show the answer
Typos like "done " or "Finished" that break the totals. Data validation limits each cell to the list, so SUMIF and the colour rule always match.
2. With the sample jobs, how much work is done but not invoiced?
- $435
- $620
- $695
Show the answer
$435. Greene Law $275 plus Rivera Home $160 is $435.
3. Why use a filter view instead of a normal filter?
- It is faster
- It only changes your view, not what others see
- It deletes the other rows
Show the answer
It only changes your view, not what others see. A normal filter changes the sheet for everyone; a filter view is a saved, personal way of looking at it.
Gmail Filters, Labels and AI Drafts With a Human Check
Your job list totals itself. Next: get Gmail to sort your inbox with filters and labels, and draft replies you check before they go out.
Start module 2 →