Use Google Sheets AI to Calculate Your Monthly Commission

What This Does

Google Sheets' AI (Gemini) can build your commission calculation formulas from plain English — so you can track exactly what you've earned and what's still pending, without needing to know any spreadsheet formulas yourself.

Before You Start

• You have a Google account (free — same as your Gmail)
• You can export your closed jobs from your CRM (JobNimbus, AccuLynx) as a CSV file, or you can type them manually
• You know your commission rate (e.g., 8% of gross, or a tiered structure)
• Time needed: 10–15 minutes to set up; 5 minutes per week to update
• Cost: Free (Google Sheets and Google account are both free)

Steps

1. Open Google Sheets and Name Your File

Go to sheets.google.com, create a new blank sheet, and name it "Commission Tracker — [Month] [Year]."

2. Set Up Your Columns

In Row 1, create these headers:

• A: Homeowner
• B: Address
• C: Contract Amount ($)
• D: Job Status
• E: Payment Received (Yes/No)
• F: Commission Rate (%)
• G: Commission Earned
• H: Pending or Paid

3. Import or Enter Your Jobs

If your CRM lets you export to CSV, download it and go to File → Import in Google Sheets to bring in your closed jobs. Otherwise, type each job manually — it takes 2 minutes per job.

In column D, use: "Installation Complete," "Paid in Full," or "Pending Payment." In column F, enter your commission rate (e.g., 0.08 for 8%).

4. Use AI to Write the Commission Formula

Click on cell G2. Look for the Gemini/AI assistant — click the sparkle icon in the toolbar or in the right sidebar. Type:

"Calculate commission by multiplying column C by column F, but only if column E says Yes"

What you should see: A formula like =IF(E2="Yes", C2*F2, 0) appears. Click Accept.

5. Use AI to Flag Pending vs. Paid

Click on cell H2. Open the AI assistant again and type:

"If column E says Yes, write Paid. If it says No, write Pending."

What you should see: A formula like =IF(E2="Yes","Paid","Pending") appears.

6. Copy Formulas Down and Add Totals

Select G2:H2, then drag the corner down through all your job rows. Then add a total row at the bottom — use the AI to write: "Sum all values in column G where column H says Paid" to get your total earned commission, and "Sum all values in column G where column H says Pending" for income still coming.

What you should see: Clear totals showing how much you've already been paid and how much is still owed.

Real Example

Scenario: You closed 4 jobs this month and want to know your exact commission and what's still owed to you.

What you get: Total paid: $1,920 | Total pending: $2,360 — instantly visible at the bottom of the sheet.

Tips

• Add a column for "date job completed" and use the AI to calculate how many days each pending payment has been outstanding — useful for chasing overdue commissions.
• If your commission structure is tiered (e.g., 5% on first $50K, 8% above that), ask the AI assistant: "Calculate tiered commission — 5% on amounts up to $50,000 cumulative, 8% above that" and it will write the more complex formula.
• Share this sheet read-only with your manager at end of month to prevent commission disputes.

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.