Commission Calculator based on total Amount of Sales

[IanShockey]

I need help with creating a formula for this commission structure.

The Initial formula I came up with is this.
=IF(K7>$K$4,(K7-K4)*$K$5,0)

I also need the calculation for anything above 100000 combined with this initial formula that I created so that if the amount exceeds 100000 it would not follow the 7% as it only applies to $40000-$99999
Anything above that will be at 9%.

Please help me solve this. ?

 

[Dave Patton]

Commissions 2021.xlsm
	J	K	L
1
2
3
4	Target	40,000.00	100,000.00
5	Rate	7%	9%
6
7	Total amount of Deals	141,000.00
8	Monthly Commision	7,890.00
9
1e

Cell Formulas
Range	Formula
K8	K8	=(K7>K4)*(K7-40000)*K5+(K7>L4)*(K7-L4)*(L5-K5)

 

[Dave Patton]

If you have more tiers or if you wish to use Excel's SumProduct instead of arithmetic, you can try the following.
The array of Bracket information is named aB and the array of Rate differentials is named aR.

The Table is not required.
The formula can use the ranges of information but then the table is required.

Commissions 2021.xlsm
	D	E	F	G
1	Total sales	141,000.00
2	Commission	7,890.00
3	Commission Arithmetic	7,890.00
4
5
6	Brackets	Rates
7	0	0.0%		0.00
8	40,000	7.0%		4,200.00
9	100,000	9.0%		3,690.00
10
1e

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--(E1>aB),E1-aB,aR)
E3	E3	=SUM(G7:G9)
G7:G9	G7	=MAX(0,MIN($E$1,D8)-D7)*E7