Excel Calculate Commission Formula: Examples for Flat and Tiered Rates

To calculate commission in Excel, multiply eligible sales by the applicable rate, then adjust the formula if the plan uses thresholds or tiers.

These examples show how to set up both simple and graduated commission plans and check their results.

Calculate commission at a flat rate

For a single commission rate applied to every sale, multiply the sales amount by the rate.

Put each salesperson’s sales in column B and the commission rate in cell E1.

Enter this in C2:

=B2*$E$1

Format E1 as a percentage.

If B2 contains 5,000 and E1 contains 8%, C2 returns 400.

The dollar signs keep the rate reference fixed when you fill the formula down the column.

If the rate is entered directly in the formula, use =B2*8%.

A cell reference is generally easier to maintain because you can change the rate without editing each formula.

Pay commission only after a sales threshold

Some plans pay commission only on the portion of sales above a minimum.

Put the threshold in E2 and the rate in E1.

To calculate commission on sales above the threshold, use:

=MAX(0,B2-$E$2)*$E$1

With sales of 5,000, a threshold of 1,000, and a rate of 8%, the result is 320: (5,000 − 1,000) × 8%.

If sales are 800, the result is 0, rather than a negative commission.

MAX makes that lower-than-threshold case explicit.

This is different from a plan that pays commission on all sales once a threshold is reached.

For that rule, use:

=IF(B2>=$E$2,B2*$E$1,0)

With the same threshold and rate, sales of 5,000 produce 400 under this formula.

Check the plan’s wording: “commission on sales above the threshold” and “commission after reaching the threshold” can mean different calculations.

Calculate commission using graduated tiers

In a graduated plan, each rate applies only to sales within its band.

For example, the first 1,000 earns 5%, the next 2,000 earns 8%, and sales above 3,000 earn 10%.

If sales are in B2, this formula calculates each band separately:

=MIN(B2,1000)*5%+MAX(MIN(B2-1000,2000),0)*8%+MAX(B2-3000,0)*10%

At 4,000 in sales, the calculation is 50 + 160 + 100, so the commission is 310.

At 800, it is 40.

The MIN and MAX functions cap each band and prevent negative amounts when sales have not reached a tier.

Keep tier boundaries and rates in worksheet cells if the plan changes often.

For a small, fixed number of tiers, a formula with explicit bands is easy to inspect.

For a larger schedule, a structured lookup or a calculation that adds the commission earned in each band may be easier to maintain.

Confirm whether the plan is graduated or retroactive: in a retroactive plan, reaching a tier can change the rate applied to all eligible sales.

Use a commission rate table

If the rate depends on a sales threshold, store the minimum sales levels in ascending order in E2:E4 and the corresponding rates in F2:F4.

For example, use thresholds of 0, 2,500, and 5,000 with rates of 5%, 7%, and 10%.

In Excel versions with XLOOKUP, find the rate for B2 with:

=XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,,-1)

Then calculate commission on all sales at that rate with =B2*XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,,-1).

For 4,000 in sales, the selected rate is 7%, producing 280.

This is a threshold or retroactive rate calculation; it does not split sales across bands.

For compatibility with older Excel versions, use VLOOKUP with an approximate match.

The threshold column must be sorted from smallest to largest:

=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)

XLOOKUP is available in Microsoft 365 and newer Excel releases, but not in some older perpetual editions.

VLOOKUP with TRUE is supported in many older versions.

Google Sheets also supports these functions, though function availability and locale-specific argument separators can vary.

Handle returns, blanks, and errors

Decide whether returns reduce eligible sales before calculating commission.

If negative sales should never create a negative payout, wrap the flat-rate calculation in MAX:

=MAX(0,B2)*$E$1

If negative amounts represent valid chargebacks, do not clamp them without checking the compensation rules.

Likewise, determine whether commission is calculated on gross sales, net sales after discounts, or net sales after refunds; the input column should match that definition.

To display a blank when no sales value has been entered, use:

=IF(B2="","",B2*$E$1)

This keeps unused rows visually clear.

If B2 contains text that should be treated as invalid, consider data validation or an explicit error check rather than silently converting it to zero.

Avoid using IFERROR to hide every problem unless a blank result is the intended response, since it can conceal a broken reference or unexpected input.

Check the formula before filling it down

• Confirm the rate is stored as a percentage: 8% and 0.08 are equivalent, while entering 8 as a number multiplies sales by eight.
• Use absolute references such as $E$1 for fixed rates and thresholds, and relative references such as B2 for each row’s sales.
• Test values below, exactly at, and above every threshold.
  
  For example, check 999, 1,000, and 1,001 for a 1,000 threshold.
• Verify whether tiers are graduated or retroactive, and whether returns or discounts count against eligible sales.
• Format commission results as currency and compare a few rows with a hand calculation.

Once the sample results match the written plan, copy the formula down for the remaining sales rows.

If a formula returns an unexpected value, check the rate cell’s format, the threshold table’s sort order, and whether the plan applies rates to each band or to total sales.