Cellwise Try it free

Excel guide · 3 min read

How to calculate sales commission in Excel

Quick answerFor a flat rate, multiply sales by the rate in a fixed cell: =B2*$D$2. For a tiered plan where the whole amount gets one rate, look the rate up from a sorted tier table with =B2*VLOOKUP(B2,$F$2:$G$4,2,TRUE). If each slice of sales earns its own rate, like tax brackets, use SUMPRODUCT over the tier table instead.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGHIJK
1RepSalesFlat rateTier fromRateRate stepQuotaCap
2Priya Nair425000.0500.030.03250004000
3Marcus Webb18200200000.050.02
4Elena Ruiz67800500000.070.02
5Tom Becker9750
6Aisha Khan31000

Download this sheet as an Excel practice file (.xlsx)

Flat-rate commission

Put the rate in its own cell (D2, 5%) and lock it with dollar signs so you can fill the formula down the whole team. When the rate changes next quarter, you edit one cell instead of every formula.

Priya sold $42,500, so at 5% she earns $2,125.

=B2*$D$2

Result: 2125

$42,500 x 5% = $2,125 commission.

Tiered rate on the whole amount

Many plans pay one rate on the entire sale depending on how much was sold: 3% under $20,000, 5% from $20,000, 7% from $50,000. Put the lower bound of each tier in F2:F4 sorted smallest first, and the rate next to it.

VLOOKUP with TRUE finds the largest "from" value that is not bigger than the sales amount. Elena sold $67,800, which lands in the 50,000 tier, so all of it is paid at 7%. In Excel 365 or 2021 and later, =B4*XLOOKUP(B4,$F$2:$F$4,$G$2:$G$4,,-1) does the same and does not need the table sorted.

=B4*VLOOKUP(B4,$F$2:$G$4,2,TRUE)

Result: 4746

$67,800 x 7% = $4,746.

Stepped (marginal) commission: each slice at its own rate

Other plans work like tax brackets: the first $20,000 earns 3%, the next $30,000 earns 5%, and only the part above $50,000 earns 7%. Ask which kind your plan is, because the two give very different numbers for the same sales.

The neat trick is a "rate step" column (H) holding how much each tier adds to the one below: 3%, then +2%, then +2%. For every tier the rep has passed, SUMPRODUCT multiplies the sales above that tier's start by the extra rate and adds it all up.

=SUMPRODUCT((B4>$F$2:$F$4)*(B4-$F$2:$F$4)*$H$2:$H$4)

Result: 3346

$3,346: $600 on the first $20,000, $1,500 on the next $30,000 and $1,246 on the last $17,800.

Quota: commission only above a target

Some plans pay nothing until the rep hits quota. IF checks the target first. Marcus sold $18,200 against a $25,000 quota, so he earns nothing this period.

A variation pays commission only on the amount above quota. Use =MAX(0,B2-$J$2)*$D$2 for that: Priya would earn 5% of $17,500, which is $875.

=IF(B3>=$J$2,B3*$D$2,0)

Result: 0

0, because $18,200 is under the $25,000 quota.

Cap the payout

If commission is capped, wrap the calculation in MIN with the cap. Elena's tiered commission of $4,746 is above the $4,000 cap in K2, so she is paid the cap.

=MIN(B4*VLOOKUP(B4,$F$2:$G$4,2,TRUE),$K$2)

Result: 4000

$4,000, because the uncapped $4,746 is above the cap.

Total commission for the team

With a flat rate you can total the payout in one step: total sales times the rate. For tiered plans, put each rep's commission in a column and SUM it, because the rate depends on each person's own sales, not the team total.

=SUM(B2:B6)*$D$2

Result: 8462.5

$169,250 in sales at 5% means $8,462.50 in commission.

Common mistakes

Tier table sorted the wrong way

Approximate-match VLOOKUP needs the "from" column sorted smallest to largest, starting at 0. Otherwise it returns the wrong rate or #N/A. XLOOKUP with match mode -1 does not need sorting.

Using upper limits instead of lower bounds

Write each tier as where it starts (0, 20000, 50000), not where it ends. A table of 19999, 49999 puts sales of $20,000 in the wrong tier.

Rate typed as 5 instead of 5%

A rate of 5 multiplies sales by five. Type 5% or 0.05 so the cell holds 0.05.

Mixing up tiered and stepped plans

Whole-amount tiers jump at the boundary ($19,999 earns $599.97, $20,000 earns $1,000); stepped plans rise smoothly. Check a value either side of each boundary against the plan document.

FAQ

What is the formula for commission in Excel?

Commission is sales times rate: =B2*$D$2 with the rate in D2. Lock the rate cell with dollar signs so it stays put when you fill the formula down.

How do I calculate tiered commission in Excel?

Make a table with each tier's starting amount and rate, sorted ascending, then use =B2*VLOOKUP(B2,table,2,TRUE). For plans where each slice gets its own rate, use the SUMPRODUCT rate-step formula.

Can I use IF instead of a tier table?

Yes, for example =B2*IF(B2>=50000,7%,IF(B2>=20000,5%,3%)). It works for two or three tiers, but a table is easier to update and check.

How do I calculate commission percentage from an amount?

Divide the commission by the sales: =commission/sales, then format as a percentage. $2,125 on $42,500 is 5%.

Functions used

Related guides

Updated