Calculate Sales Commission With a Flat or Tiered Rate

Excel & Google Sheets

How it works

For a flat commission, multiply sales (B2) by the rate (C2) — keep the rate in its own column so changing it never means editing formulas. For tiered plans where the rate depends on the sales total, nest IF: =B2*IF(B2>=50000,0.08,IF(B2>=25000,0.06,0.04)) pays 8% at $50,000 and up, 6% from $25,000, and 4% below that. IF checks the conditions top to bottom and stops at the first true one, which is why the highest threshold must come first. Note this version applies one rate to the whole amount — a rep crossing a tier gets the better rate on everything, so payouts jump at the thresholds.

B2

The rep's total sales for the period.

*C2

Times the commission rate — store 5% as 0.05 or format the cell as a percent.

When to use it

Use this for monthly commission runs, sales contests, referral payouts, or any pay-for-performance calculation. A visible formula the whole team can check prevents payout disputes.

Common mistakes

• ✕Entering the rate as 5 instead of 5% or 0.05.

  ✓=B2*5 pays five times the sale. Enter the rate as 0.05, or type 5% so the spreadsheet stores it correctly.

• ✕Ordering tiered IF conditions from lowest to highest.

  ✓=IF(B2>=25000,0.06,IF(B2>=50000,0.08,0.04)) never reaches the 8% tier — a $60,000 rep matches >=25000 first. Test the highest threshold first: =B2*IF(B2>=50000,0.08,IF(B2>=25000,0.06,0.04)).

• ✕Expecting the tiered formula to pay each rate only on the slice above its threshold.

  ✓This version applies one rate to the full amount, so payouts jump at thresholds. If your plan pays marginal rates per bracket (like tax brackets), that needs a different, additive formula.