Commission Formula in Excel: Flat, Tiered & More

Calculating commission in Excel can be as simple as multiplying sales by a percentage.

But real commission plans are rarely that simple.

A salesperson may earn nothing until a target is reached. A higher rate may apply after a certain sales level. The commission may be progressive, meaning different portions of the sale earn different rates. There may also be bonuses, caps, accelerators, product-specific rates, or commission based on gross profit instead of revenue.

That is why there is no single commission formula in Excel that works for every business.

The correct formula depends on how the commission plan is structured.

In this guide, you will learn how to calculate commission in Excel for the most common real-world scenarios, including flat-rate commissions, target-based commissions, tiered rates, progressive commission structures, caps, bonuses, accelerators, gross-profit commissions, and dynamic rate tables.

More importantly, we will separate two commission structures that are often confused: a single rate determined by the final sales level and a progressive commission where each portion of sales is paid at a different rate.

That distinction changes the formula completely.

Commission Formula in Excel: Quick Answer

For a simple commission where Sales are in B2 and the Commission Rate is in C2, the formula is:

=B2*C2

If sales are $25,000 and the commission rate is 5%, the result is:

$25,000 x 5% = $1,250

That is the basic commission calculation.

From there, the formula changes depending on the rules of the commission plan.

Commission Plan	Example Formula
Flat commission	=Sales*Rate
Commission after reaching target	=IF(Sales>=Target,Sales*Rate,0)
Commission only above target	=MAX(Sales-Target,0)*Rate
Flat commission with payout cap	=MIN(Sales*Rate,Cap)
Commission plus target bonus	=Sales*Rate+IF(Sales>=Target,Bonus,0)
Progressive commission	=Tier1AmountRate1+Tier2AmountRate2+Tier3Amount*Rate3
Rate selected from a table	=Sales*INDEX(Rates,MATCH(Sales,Thresholds,1))

These are formula patterns rather than copy-and-paste formulas. The examples below show the exact cell references for each worksheet layout.

We will build each of these step by step.

Note: Each example in this guide uses its own worksheet layout. Cell references such as B2, C2, and D2 may represent different inputs from one example to another, so check the table above each formula before copying it into your workbook.

What Is Sales Commission?

Sales commission is compensation calculated from an employee's or contractor's sales performance.

For example, a salesperson who earns 5% commission on $40,000 of sales receives:

$40,000 x 5% = $2,000

In Excel, that becomes:

=40000*5%

Or, more realistically, if the values are stored in cells:

=B2*C2

The important part is not the multiplication itself.

The important part is determining what amount should receive what rate.

That is where commission models begin to differ.

A business might pay 5% on every sale. Another might pay 5% until $10,000, 7% until $20,000, and 10% above that. Another may pay 10% on all sales once the salesperson passes $20,000.

Those last two examples may both be described as "tiered commission," but mathematically they are completely different.

Before building your Excel formula, you need to understand which one you actually have.

Prepare the Commission Data in Excel

For a basic commission calculation, your worksheet might look like this:

A	B	C	D
Salesperson	Sales	Commission Rate	Commission
Emma	$25,000	5%
Daniel	$38,000	5%
Sofia	$46,500	6%

The commission formula in D2 is:

=B2*C2

Copy the formula down to calculate commission for every salesperson.

For larger datasets, I recommend converting the range into an Excel Table with Ctrl + T.

If your table is named Sales, the same formula can use structured references:

=[@Sales]*[@[Commission Rate]]

Structured references are easier to read and automatically fill down when new records are added.

Basic Flat-Rate Commission Formula in Excel

The simplest commission structure applies one fixed percentage to all eligible sales.

Assume:

Cell	Value
B2	$30,000 Sales
C2	5% Commission Rate

Use:

=B2*C2

The result is:

$30,000 x 5% = $1,500

This method works well when the rate does not depend on performance levels, targets, products, customers, or any other condition.

Store the Rate in a Cell Instead of the Formula

You could write:

=B2*5%

But storing the commission rate in a separate cell is usually better:

=B2*C2

If the rate later changes from 5% to 6%, you change the value in C2 rather than editing the formula.

This becomes much more important in large commission models.

Round Commission to Two Decimal Places

If commission payments need to be stored to the nearest cent, wrap the calculation in ROUND:

=ROUND(B2*C2,2)

This is useful when later calculations depend on the stored commission value.

Excel may display two decimal places through formatting while internally storing additional decimals. ROUND actually changes the calculated result to two decimal places.

For payroll or payout calculations, that distinction can matter.

If commission is calculated across many individual transactions and later summarized with SUM or SUMIFS, decide whether your policy requires each transaction to be rounded before it is added or only the final payout to be rounded. These two approaches can produce slightly different totals. This is covered again in the Rounding Too Early section below.

Commission Formula with a Sales Target

Suppose commission is paid only if the salesperson reaches a minimum target.

Assume:

B	C	D
Sales	Target	Rate
$24,000	$20,000	5%

Use:

=IF(B2>=C2,B2*D2,0)

Excel checks whether Sales are greater than or equal to the Target.

If the target has been reached, the commission is calculated on the full sales amount. Otherwise, the formula returns zero.

For $24,000 in sales:

$24,000 x 5% = $1,200

For $18,000 in sales with the same $20,000 target, commission is zero.

Commission Only on Sales Above the Target

This is a different plan.

Suppose the salesperson earns 5% only on the amount exceeding a $20,000 target.

If sales are $24,000, only $4,000 is commissionable.

Use:

=MAX(B2-C2,0)*D2

The result is:

($24,000 - $20,000) x 5%
= $4,000 x 5%
= $200

The MAX function prevents the formula from returning a negative commission when sales are below target.

This distinction is important:

Plan	$24,000 Sales, $20,000 Target, 5% Rate
5% on all sales after target is reached	$1,200
5% only on sales above target	$200

Both can be described as "commission after target," but they are not the same calculation.

Commission with a Target Bonus

Some compensation plans combine regular commission with a fixed bonus for reaching a target.

Assume:

B	C	D	E
Sales	Rate	Target	Bonus
$30,000	5%	$25,000	$500

Use:

=B2*C2+IF(B2>=D2,E2,0)

The normal commission is:

$30,000 x 5% = $1,500

Because the $25,000 target was reached, the salesperson also receives the $500 bonus.

Total payout:

$1,500 + $500 = $2,000

If sales were below $25,000, only the standard commission would be paid.

Tiered Commission in Excel: First Understand the Type of Tier

This is where commission calculations become more interesting.

Suppose your commission structure is:

Sales	Rate
Up to $10,000	5%
$10,000.01 to $20,000	7%
Above $20,000	10%

If someone sells $25,000, what is the commission?

There are two possible answers.

Method 1: One Rate Applies to All Sales

If reaching $20,000 unlocks a 10% rate on the entire sales amount:

$25,000 x 10% = $2,500

This is a rate-based, bracket, or sometimes cliff-style commission structure.

Method 2: Each Sales Tier Has Its Own Rate

If the first $10,000 earns 5%, the next $10,000 earns 7%, and only sales above $20,000 earn 10%:

First $10,000 x 5% = $500
Next $10,000 x 7% = $700
Remaining $5,000 x 10% = $500
Total Commission = $1,700

This is a progressive or marginal tiered commission structure.

The difference is significant:

Method	Commission on $25,000
One 10% rate on all sales	$2,500
Progressive tiers	$1,700

Before choosing a formula, confirm how the commission agreement defines the tiers.

Tiered Commission Using IF

For a structure where one rate applies to the entire sales amount based on the final sales level, IF works well when there are only a few tiers.

Using these rates:

Sales	Rate
Up to $10,000	5%
Up to $20,000	7%
Above $20,000	10%

With Sales in B2:

=IF(B2<=10000,B2*5%,IF(B2<=20000,B2*7%,B2*10%))

If B2 contains $25,000:

$25,000 x 10% = $2,500

Excel first checks whether sales are $10,000 or below.

If not, it checks whether sales are $20,000 or below.

Anything higher receives the 10% rate.

Return the Rate Separately

Sometimes it is better to calculate the commission rate in one column and the commission amount in another.

Rate formula:

=IF(B2<=10000,5%,IF(B2<=20000,7%,10%))

Then commission:

=B2*C2

This makes the worksheet easier to audit because you can see both the rate Excel selected and the resulting payout.

Tiered Commission Using IFS

For newer Excel versions, IFS makes multiple conditions easier to read.

=B2*IFS(
B2<=10000,5%,
B2<=20000,7%,
B2>20000,10%
)

The logic is identical to the nested IF formula.

The advantage is readability.

Instead of nesting several functions inside one another, each condition and result is written as a pair.

For a small and stable commission plan, this is perfectly reasonable.

For a commission plan where thresholds change regularly, however, storing the tiers in a separate table is usually better.

Dynamic Commission Rates Using a Lookup Table

Hardcoding values such as 10000, 20000, 5%, and 10% directly into formulas is manageable when the commission structure rarely changes.

It becomes inconvenient when rates are reviewed every quarter or when different teams use different plans.

A better structure is to create a commission rate table.

For example:

G	H
Minimum Sales	Rate
$0	5%
$10,000	7%
$20,000	10%
$50,000	12%

The first column contains the point at which each rate begins.

For the INDEX MATCH method below, the threshold values must be sorted from smallest to largest. The XLOOKUP version does not technically require sorting when using match_mode -1 with the default search mode, although keeping commission thresholds in ascending order is still recommended because it makes the table easier to review and maintain.

Commission Rate Using INDEX MATCH

If Sales are in B2, use:

=INDEX($H$2:$H$5,MATCH(B2,$G$2:$G$5,1))

To calculate the commission directly:

=B2*INDEX($H$2:$H$5,MATCH(B2,$G$2:$G$5,1))

If B2 is $25,000, MATCH finds the largest threshold that does not exceed $25,000.

That is $20,000.

INDEX then returns the corresponding 10% rate.

The commission becomes:

$25,000 x 10% = $2,500

This approach is much easier to maintain than rewriting a long IF formula every time a threshold changes.

You edit the rate table instead.

If you want a deeper comparison of this lookup method with VLOOKUP, see Why Use INDEX MATCH Instead of VLOOKUP in Excel?

Commission Rate Using XLOOKUP

In modern Excel versions that support XLOOKUP, the equivalent formula is:

=B2*XLOOKUP(B2,$G$2:$G$5,$H$2:$H$5,,-1)

Unlike MATCH with a match type of 1, this XLOOKUP formula does not require the lookup array to be sorted. With match_mode set to -1, XLOOKUP searches for an exact match or the next smaller value. However, keeping the thresholds in ascending order is still good spreadsheet design and allows the same rate table to work correctly with the INDEX MATCH version above.

The -1 match mode tells XLOOKUP to use an exact match or the next smaller value.

For users who need compatibility with Excel 2019, INDEX MATCH remains a strong choice.

For more examples of exact and approximate lookup behavior, see Flexible Lookups in Excel: Exact Match, Starts With, Contains, and Beyond.

Progressive Tiered Commission Formula in Excel

Now consider the progressive version.

Assume:

Sales Portion	Rate
First $10,000	5%
Next $10,000	7%
Above $20,000	10%

For sales of $25,000, the correct commission is $1,700.

One direct Excel formula is:

=MIN(B2,10000)*5%+MAX(MIN(B2,20000)-10000,0)*7%+MAX(B2-20000,0)*10%

It looks complicated at first, but each part represents one commission tier.

First Tier

=MIN(B2,10000)*5%

This limits the first tier to $10,000.

If sales are $25,000:

$10,000 x 5% = $500

Second Tier

=MAX(MIN(B2,20000)-10000,0)*7%

MIN(B2,20000) prevents this tier from exceeding $20,000.

Subtracting $10,000 removes the amount already assigned to Tier 1.

For $25,000 of sales:

$20,000 - $10,000 = $10,000
$10,000 x 7% = $700

Third Tier

=MAX(B2-20000,0)*10%

Only sales above $20,000 enter this tier.

For $25,000:

$25,000 - $20,000 = $5,000
$5,000 x 10% = $500

Total:

$500 + $700 + $500 = $1,700

This formula is excellent when the commission structure has only a few tiers and is unlikely to change.

For many tiers, a table-based approach is more scalable.

Progressive Commission with SUMPRODUCT

A progressive commission structure can also be built from a table.

Suppose the tier table is:

G	H	I
Threshold	Tier Rate	Rate Increase
$0	5%	5%
$10,000	7%	2%
$20,000	10%	3%

Column I contains the increase from the previous rate.

For the first row:

=H2

For the second row:

=H3-H2

Copy the pattern down.

With Sales in B2, the progressive commission formula becomes:

=SUMPRODUCT((B2>$G$2:$G$4)*(B2-$G$2:$G$4)*$I$2:$I$4)

For $25,000 of sales, Excel effectively calculates:

$25,000 x 5%
+
$15,000 x 2%
+
$5,000 x 3%

Which equals:

$1,250 + $300 + $150 = $1,700

At first glance, this calculation may look different from splitting sales into $10,000 blocks.

Mathematically, however, it produces the same result.

The advantage is scalability.

If another tier is added later, you extend the threshold and rate table instead of rebuilding a long formula from scratch.

For complex commission models, separating business rules from the calculation formula is usually the safer design.

If you want to understand how SUMPRODUCT evaluates arrays and conditions, see my complete guide to the SUMPRODUCT function.

Commission with an Accelerator Above Target

Many sales plans increase the commission rate once a quota has been reached.

For example:

Rule	Rate
Sales up to $100,000	5%
Sales above $100,000	8%

This is an accelerator.

Assume:

Cell	Value
B2	Sales
C2	Target
D2	Base Rate
E2	Accelerator Rate

Use:

=MIN(B2,C2)*D2+MAX(B2-C2,0)*E2

Suppose:

Sales = $120,000
Target = $100,000
Base Rate = 5%
Accelerator Rate = 8%

The formula calculates:

First $100,000 x 5% = $5,000
Remaining $20,000 x 8% = $1,600
Total Commission = $6,600

This is another progressive calculation.

The higher rate applies only to sales above the target.

If your plan instead changes the rate on all sales once the target is reached, the formula would be different:

=B2*IF(B2>=C2,E2,D2)

At $120,000, that version would calculate the full $120,000 at 8%.

Commission with a Cap

A commission cap limits the maximum payout.

Suppose:

Sales = $150,000
Commission Rate = 6%
Maximum Commission = $5,000

Without a cap:

$150,000 x 6% = $9,000

To limit commission to $5,000:

=MIN(B2*C2,D2)

Where D2 contains the commission cap.

The result is $5,000.

Payout Cap vs Sales Cap

These are not the same thing.

A payout cap limits the final commission:

=MIN(B2*C2,D2)

A sales cap limits the amount of sales eligible for commission:

=MIN(B2,D2)*C2

Suppose sales are $150,000, only the first $100,000 are commissionable, and the rate is 6%.

The second formula returns:

$100,000 x 6% = $6,000

The result is $6,000, compared with the $5,000 payout cap above. This shows why a cap on commission and a cap on commissionable sales are not interchangeable.

Always model the rule that actually appears in the compensation plan.

Commission with Both a Minimum Target and a Cap

You can combine conditions.

Suppose commission is 5% on sales above a $20,000 target, but total commission cannot exceed $3,000.

Assume:

B2 = Sales
C2 = Target
D2 = Rate
E2 = Commission Cap

Use:

=MIN(MAX(B2-C2,0)*D2,E2)

The calculation happens in two stages.

First:

MAX(B2-C2,0)*D2

calculates commission on sales above target.

Then:

MIN(...,E2)

prevents the payout from exceeding the cap.

Building formulas from small pieces like this is usually easier than creating one large nested IF.

Base Salary Plus Commission Formula

For employees who receive a fixed salary plus commission, the calculation is straightforward.

Assume:

B2 = Base Salary
C2 = Sales
D2 = Commission Rate

Use:

=B2+C2*D2

For example:

Base Salary = $3,000
Sales = $40,000
Commission Rate = 5%

The result is:

$3,000 + ($40,000 x 5%)
= $3,000 + $2,000
= $5,000

If the commission itself contains tiers or bonuses, calculate the commission separately and then add the salary.

Keeping salary and commission in separate columns also makes payroll reconciliation easier.

Commission Based on Gross Profit Instead of Sales

Not every company pays commission on revenue.

Some pay commission on gross profit.

Suppose:

B2 = Sales Revenue
C2 = Cost
D2 = Commission Rate

Gross profit is:

=B2-C2

Commission on gross profit is:

=(B2-C2)*D2

If revenue is $20,000, cost is $14,000, and the salesperson earns 6% of gross profit:

Gross Profit = $20,000 - $14,000 = $6,000
Commission = $6,000 x 6% = $360

This is very different from paying 6% of revenue, which would produce a $1,200 commission.

When building a commission spreadsheet, make sure the commission base is clearly defined as revenue, net revenue, gross profit, units, collected cash, or another measure.

Commission After Returns or Discounts

If returns reduce commissionable sales, calculate commission on net sales.

Assume:

B2 = Gross Sales
C2 = Returns
D2 = Commission Rate

Use:

=(B2-C2)*D2

To prevent negative commission:

=MAX(B2-C2,0)*D2

However, do not automatically use MAX if your company's policy allows returns or chargebacks to create negative commission.

In that situation, a negative result may be intentional.

The spreadsheet should reflect the compensation policy rather than hide an unfavorable result.

Commission Only When an Invoice Is Paid

Some businesses pay commission when the customer pays rather than when the sale is invoiced.

Suppose:

B2 = Invoice Amount
C2 = Payment Status
D2 = Commission Rate

Use:

=IF(C2="Paid",B2*D2,0)

If unpaid invoices should remain blank instead:

=IF(C2="Paid",B2*D2,"")

This approach can be expanded with payment dates, partial payments, or collected amounts if commissions are based on cash receipts.

Different Commission Rates by Product

Commission rates may depend on what was sold.

For example:

Product	Rate
Product A	4%
Product B	6%
Product C	8%

Assume:

B2 = Product
C2 = Sales Amount
G2:G4 = Product list
H2:H4 = Commission rates

Using INDEX MATCH:

=C2*INDEX($H$2:$H$4,MATCH(B2,$G$2:$G$4,0))

Because product names require an exact match, the final argument of MATCH is 0.

With XLOOKUP:

=C2*XLOOKUP(B2,$G$2:$G$4,$H$2:$H$4)

This lets one sales table support many products without hardcoding product names or rates into the commission formula.

Different Commission Rates by Salesperson

The same lookup-table approach works when different employees have different commission rates.

For example:

Salesperson	Rate
Emma	5%
Daniel	6%
Sofia	7%

If the salesperson is in A2 and sales are in B2:

=B2*INDEX($H$2:$H$4,MATCH(A2,$G$2:$G$4,0))

Or with XLOOKUP:

=B2*XLOOKUP(A2,$G$2:$G$4,$H$2:$H$4)

This is more reliable than manually entering the commission rate on every sales row.

If a person's rate changes, you update the central rate table.

Total Commission by Salesperson with SUMIFS

Once commission is calculated at transaction level, you may need a summary by salesperson.

Suppose:

Column A = Salesperson
Column D = Commission
H2 = Salesperson to summarize

Use:

=SUMIFS($D$2:$D$1000,$A$2:$A$1000,H2)

If the source data is an Excel Table named Sales:

=SUMIFS(Sales[Commission],Sales[Salesperson],H2)

This gives you the total commission for one salesperson across all transactions.

The same idea can be expanded to calculate commission by month, territory, product, department, or customer using additional SUMIFS criteria.

Monthly Commission by Salesperson

Suppose you store individual sales transactions and want to calculate commission for a particular salesperson and month.

A clean workflow is:

Sales transactions -> commission per transaction -> monthly summary

If commission has already been calculated in each transaction row, SUMIFS can summarize it.

For example, if:

A = Salesperson
B = Transaction Date
E = Commission
H2 = Salesperson
I2 = First day of month

Use:

=SUMIFS(
$E$2:$E$1000,
$A$2:$A$1000,H2,
$B$2:$B$1000,">="&I2,
$B$2:$B$1000,"<"&EDATE(I2,1)
)

This includes transactions from the first day of the selected month up to, but not including, the first day of the following month.

Using date boundaries this way is generally safer than comparing month names stored as text.

Make Complex Commission Formulas Easier to Read with LET

When several rules are combined, commission formulas can become difficult to audit.

The LET function allows you to assign meaningful names to parts of the formula.

For example, consider an accelerator structure:

=MIN(B2,C2)*D2+MAX(B2-C2,0)*E2

Using LET:

=LET(
sales,B2,
target,C2,
base_rate,D2,
accelerator_rate,E2,
MIN(sales,target)*base_rate+MAX(sales-target,0)*accelerator_rate
)

The result is identical.

But the second formula explains itself.

Someone reviewing the workbook can immediately see what sales, target, base_rate, and accelerator_rate represent.

This becomes especially useful when commission calculations contain several thresholds, bonuses, or adjustments.

For simple calculations such as =B2*C2, however, using LET would add unnecessary complexity.

If you are new to LET, my complete guide to the Excel LET function explains how named variables work and when they actually improve a formula.

Handle Blank Cells in Commission Formulas

Suppose sales or commission rate has not yet been entered.

The basic formula:

=B2*C2

may return zero.

If you prefer the commission cell to remain blank until both inputs exist:

=IF(OR(B2="",C2=""),"",B2*C2)

With rounding:

=IF(OR(B2="",C2=""),"",ROUND(B2*C2,2))

This often produces cleaner reports, especially when formulas have been pre-filled into hundreds of future rows.

Avoid Hardcoded Commission Rules Where Possible

A formula like this works:

=IF(B2<=10000,B2*5%,IF(B2<=20000,B2*7%,B2*10%))

But every business rule is hidden inside the formula.

If the first threshold changes from $10,000 to $12,500, someone must edit the formula.

If the rate changes from 7% to 7.5%, the formula must be edited again.

And if the formula exists across several sheets, each copy becomes another place where something can go wrong.

For small one-off calculations, hardcoding is fine.

For a real commission model, a better design is usually:

Input data
+
Commission rule table
+
Calculation columns
+
Summary report

The spreadsheet becomes easier to update, easier to audit, and easier to hand over to another person.

Common Commission Formula Mistakes in Excel

The mathematics behind commission is usually simple. Most errors come from translating the compensation rules incorrectly.

Confusing Progressive Tiers with a Single Tier Rate

This is the biggest mistake.

If sales of $25,000 fall into a 10% bracket, determine whether 10% applies to:

all $25,000

or only:

the portion above the previous threshold

The difference can materially change payroll.

Incorrect Tier Boundaries

Be precise about whether a threshold belongs to the lower or upper tier.

For example:

Up to and including $10,000 = 5%
Above $10,000 = 7%

can be written as:

=IF(B2<=10000,5%,7%)

Using <10000 instead would move exactly $10,000 into the higher tier.

Using an Approximate Lookup on an Unsorted Table

When using:

=MATCH(B2,$G$2:$G$5,1)

the threshold table must be sorted in ascending order.

An incorrectly sorted rate table can return the wrong rate.

Mixing Sales and Commissionable Sales

The sales figure shown in the CRM or invoice report may not be the amount on which commission is actually paid.

Commissionable sales may exclude returns, taxes, shipping, discounts, unpaid invoices, non-eligible products, or certain customers.

Calculate the commission base first.

Then apply the rate.

Capping the Wrong Value

A cap on commission payout is not the same as a cap on commissionable sales.

Use:

=MIN(B2*C2,D2)

for a payout cap.

Use:

=MIN(B2,D2)*C2

for a sales cap.

Rounding Too Early

If commission is calculated across many rows, rounding every transaction before adding the results can produce a slightly different total from adding the unrounded values first and rounding only the final payout.

For example, a payroll model may calculate commission at transaction level and then summarize it with SUMIFS. In that case, decide whether the business policy requires each transaction to be rounded to cents or only the final salesperson payout.

Neither approach is universally correct. Your Excel model should follow the company's actual rounding policy.

Hiding Errors with IFERROR

It can be tempting to write:

=IFERROR(your_formula,0)

everywhere.

But returning zero for every error can hide incorrect product names, missing rates, or broken lookup tables.

If a rate lookup fails, seeing #N/A may be safer than silently paying zero commission.

Fix the source of the error before hiding it.

If you do want to handle a missing rate, return a meaningful message instead of zero. For example:

=IFERROR(INDEX($H$2:$H$4,MATCH(B2,$G$2:$G$4,0)),"Rate not found")

With XLOOKUP, you can handle the missing value directly without IFERROR:

=XLOOKUP(B2,$G$2:$G$4,$H$2:$H$4,"Rate not found")

When a function already provides a specific way to handle a missing lookup, prefer that over wrapping the entire formula in IFERROR. IFERROR catches every error, including errors you may actually want to investigate.

Which Commission Formula Should You Use?

There is no "best" commission formula in isolation.

The best formula is the simplest formula that accurately represents the commission agreement.

Situation	Recommended Approach
One fixed commission rate	Simple multiplication
Commission paid only after target	IF
Commission only on amount above target	MAX
A few rate brackets	IF or IFS
Rates maintained in a table	INDEX MATCH or XLOOKUP
Progressive tiers	MIN/MAX or SUMPRODUCT
Commission accelerator	MIN + MAX
Maximum commission payout	MIN
Different rates by product or salesperson	Lookup table
Monthly salesperson totals	SUMIFS
Complex readable formulas	LET

Do not make a formula more sophisticated simply because Excel allows it.

If =B2*C2 accurately represents the plan, use it.

If the commission agreement has six progressive tiers, bonuses, caps, and different product rates, then a structured model with lookup tables and helper calculations is usually better than one enormous formula.

A Practical Structure for a Sales Commission Spreadsheet

For a commission workbook that will be reused every month, separating inputs, rules, calculations, and reporting is more important than trying to fit everything into one sheet.

A practical workbook could contain a transaction table with:

Column	Purpose
Date	Sale or invoice date
Salesperson	Employee or contractor
Customer	Customer name
Product	Product or service
Gross Sales	Original amount
Adjustments	Returns or discounts
Commissionable Sales	Eligible amount
Commission Rate	Rate selected by formula
Commission	Calculated payout
Status	Paid, Pending, Approved, etc.

Commission rules can then be stored in a separate area or worksheet.

For example:

Threshold	Rate
$0	5%
$10,000	7%
$20,000	10%
$50,000	12%

This keeps the raw transactions separate from the compensation assumptions.

It also makes it easier to review whether a wrong payout comes from the transaction data, the commission rules, or the formula itself.

Commission Formula Examples at a Glance

Here is a compact reference for the formulas covered in this guide.

Scenario	Formula
Basic commission	=Sales*Rate
Rounded commission	=ROUND(Sales*Rate,2)
Commission after target	=IF(Sales>=Target,Sales*Rate,0)
Commission above target only	=MAX(Sales-Target,0)*Rate
Target bonus	=Sales*Rate+IF(Sales>=Target,Bonus,0)
Single-rate tiers	=IF(Sales<=Tier1,SalesRate1,IF(Sales<=Tier2,SalesRate2,Sales*Rate3))
Dynamic tier lookup	=Sales*INDEX(Rates,MATCH(Sales,Thresholds,1))
Progressive tiers	=MIN(Sales,Tier1)*Rate1+MAX(MIN(Sales,Tier2)-Tier1,0)*Rate2+MAX(Sales-Tier2,0)*Rate3
Accelerator	=MIN(Sales,Target)*BaseRate+MAX(Sales-Target,0)*AcceleratorRate
Commission cap	=MIN(Sales*Rate,Cap)
Gross profit commission	=(Revenue-Cost)*Rate
Paid invoice only	=IF(Status="Paid",InvoiceAmount*Rate,0)
Product-specific rate	=Sales*INDEX(Rates,MATCH(Product,Products,0))
Total by salesperson	=SUMIFS(Commission,Salesperson,SelectedSalesperson)

Frequently Asked Questions

What is the commission formula in Excel?

For a basic commission calculation, multiply the sales amount by the commission rate:

=Sales*CommissionRate

For example, if sales are in B2 and the rate is in C2:

=B2*C2

How do I calculate 5% commission in Excel?

If the sales amount is in B2, use:

=B2*5%

A better reusable setup is to store 5% in another cell, such as C2, and use:

=B2*C2

How do I calculate commission only after a target is reached?

If Sales are in B2, Target in C2, and Commission Rate in D2:

=IF(B2>=C2,B2*D2,0)

This applies commission to all sales once the target is reached.

How do I calculate commission only on sales above the target?

Use:

=MAX(B2-C2,0)*D2

Only the amount exceeding the target earns commission.

How do I calculate tiered commission in Excel?

First determine whether the rate applies to all sales after a tier is reached or whether each portion of sales earns a different rate.

For a single rate based on final sales level, IF, IFS, INDEX MATCH, or XLOOKUP can be used.

For progressive tiers, use a formula based on MIN and MAX or create a scalable tier table with SUMPRODUCT.

What is the difference between tiered and progressive commission?

In a single-rate tier structure, the final sales level determines one rate that is applied to the entire sales amount.

In a progressive commission structure, different portions of the sales amount earn different rates.

For example, at $25,000 of sales, a 10% final-tier rate could pay $2,500, while a progressive 5% / 7% / 10% structure could pay only $1,700.

How do I put a maximum cap on commission in Excel?

If calculated commission is B2*C2 and the maximum payout is stored in D2, use:

=MIN(B2*C2,D2)

Can Excel calculate commission for different salespeople automatically?

Yes.

Store each salesperson and their rate in a lookup table, then use INDEX MATCH or XLOOKUP to retrieve the correct rate automatically.

You can then use SUMIFS to calculate total commission by salesperson, month, product, territory, or other criteria.

Should commission rates be hardcoded into formulas?

For small, temporary calculations, hardcoded rates can be acceptable.

For a commission model that will be reused, storing thresholds and rates in a separate table is usually easier to maintain and audit.

Final Thoughts

A basic commission formula in Excel takes only a few seconds to build:

=Sales*CommissionRate

But that formula is only correct when the commission plan itself is simple.

As soon as targets, tiers, accelerators, bonuses, caps, products, or different employees are introduced, the first step should not be writing a longer formula.

The first step should be understanding the rule.

Does the higher rate apply to the entire sales amount or only the amount inside that tier?

Is commission paid on revenue, gross profit, or collected cash?

Does reaching a target unlock commission on all sales or only on sales above the target?

Is the cap applied to sales or to the final payout?

Once those questions are clear, the Excel calculation usually becomes straightforward.

For simple commission plans, functions such as IF, MIN, and MAX are often enough. For changing rate structures, lookup tables with INDEX MATCH or XLOOKUP make the workbook easier to maintain. And for progressive tiered commission, SUMPRODUCT provides a scalable alternative to increasingly long nested formulas.

The goal is not to build the most impressive formula.

It is to build a commission model that gives the correct answer, makes the business rules visible, and can still be understood when someone opens the workbook six months from now.