# Calculating a tiered commission

#### ckoehl

##### New Member
I am setting up a spreadsheet to calculate sales commissions. The tiers are:
\$0-\$40,000 @ 50/50 \$40,001-50,000 @ 60/40 \$50,001-\$60,000 @ 70/30 \$60,001-\$70,000 @80/20 \$70,001 & up @ 90%
It is cumulative for the year

Here are my columns if that helps with how I need to lay it out:
Gross Commission YTD Gross Emp Commission Company side

Hope that makes sense! Thanks!

#### joeu2004

##### Well-known Member
I do not understand your notation for commission rates (50/50, 60/40, etc). And a commission rate of 90% of the amount over 70,000 seems insane. That cannot possibly be what you mean.

The following is a paradigm that you might be able to customize with the correct commission rates.

A
B
C
1
Over...​
Rate​
Diff Rate​
2
0​
10%​
10%​
3
40000​
12%​
2%​
4
50000​
15%​
3%​
5
60000​
20%​
5%​
6
70000​
25%​
5%​

<tbody>
</tbody>

The formula in C2 is =B2-N(B1) . Copy C2 into C3:C6.

Then with the sales amount in S1, the commission is:

=SUMPRODUCT((S1>\$A\$2:\$A\$6)*(S1-\$A\$2:\$A\$6), \$C\$2:\$C\$6)

formatted as Currency.

Last edited:

#### ckoehl

##### New Member
Thanks for your help Joe, yes, I posted an example of a commission structure that we offer. Maybe you misunderstood our basis, once they hit 70k in gross closed commission, they make 90% of additional commission splits. I will try the formula though!