Commission tracking is one of those things that looks simple until you're actually doing it. Flat rate commissions are easy. But the moment you add tiers, accelerators, draw amounts, or splits, the spreadsheet gets complicated fast, and one wrong formula means a rep gets paid the wrong amount.
This guide walks through how to build a commission tracker in Google Sheets that handles flat rates, tiered structures, and monthly summaries. You'll come away with something you can actually maintain without rereading it every month.
Start with a Clean Deal Log
Your commission tracker needs to start from a flat deal log — one row per closed deal with at minimum: rep name, deal value, close date, and commission rate (or tier label). If you're pulling this from a CRM, export it and paste it into Sheet 1.
Don't try to calculate commissions directly in the CRM export. Work from a clean copy so you can add helper columns without worrying about overwriting source data.
Flat Rate Commissions
For a flat commission rate, add a Commission column with:
=D2*E2
Where D2 is deal value and E2 is the commission rate as a decimal (0.08 for 8%). Copy this down for every row. Done.
Tiered Commissions
Tiered commissions pay different rates depending on how much a rep has sold in the period. A common structure: 6% on the first $50k, 8% on $50k–$100k, 10% above $100k.
First, calculate each rep's cumulative sales to date in the period using SUMIF:
=SUMIF($B$2:$B2, B2, $D$2:$D2)
This gives a running total of each rep's sales as you go down the sheet. Then use a nested IF to apply the right rate based on where that cumulative total falls:
=IF(F2<=50000, D2*0.06, IF(F2<=100000, D2*0.08, D2*0.10))
Where F2 is the cumulative sales column. This applies the marginal rate for each deal based on the tier the rep is in at that point.
Monthly Commission Summary
Once your deal log has commission values, build a monthly summary with a pivot table. Go to Data, Pivot table, insert on a new sheet. Add Rep to Rows, add Month (from your close date column — you can use =TEXT(C2,"MMM YYYY") as a helper column) to Columns, and add Commission to Values set to SUM.
Now you have a grid showing each rep's commission by month. Add a Grand Total row to see total commission payouts per month.
Handling Splits
If deals are sometimes split between two reps, add a Split % column to your deal log and a Co-Rep column. Calculate each rep's portion:
=D2*E2*G2
Where G2 is the split percentage for the primary rep (0.6 for 60%). Create a second row for the co-rep with their percentage. This keeps the pivot table accurate when you summarize by rep.
The Easy Way: Using SheetXAI in Google Sheets
Commission trackers involve enough moving parts that building them manually takes real time. SheetXAI handles it directly inside Google Sheets from a single description.
Example 1: Your deal data is already in the sheet.
"Build a commission tracker from the deal log on Sheet 1. Use tiered rates: 6% on deals up to $50k cumulative, 8% from $50k to $100k, 10% above $100k. Add a monthly summary by rep."
SheetXAI reads your data, adds the cumulative sales column, applies the tiered formula, and builds the monthly pivot summary.
Example 2: Your data lives in your CRM.
"Pull closed-won deals from this quarter from Salesforce and build a commission tracker with tiered rates and a rep summary showing total commission owed per person."
SheetXAI pulls the deals, calculates commissions, and produces the summary ready to send to finance.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a Sales Pipeline Tracker in Google Sheets, SUMIF in Google Sheets, and Google Sheets AI Guide.
