Cumulative tiered commission & exclude big deals from cumulative calc

[CommAnalyst]

	Tier	Rate	Differential Rate
1	0	5%	5%
2	500,001	10%	5%
3	100,0001	15%	5%
4	1,500,001	20%	5%
5
6	Deal 1 Amount	1,000,000
7	Commission	=sumproduct((A1:A4<B6)*(B5-A1:A4)*(C1:C4))	=75,000

<tbody>
</tbody>

I have a tiered comm calculation using the above formula for the 1st deal the person completes.
Question/Problem: How do I calculate the subsequent comm, if the rate is based on the accumulation of previous deals tiering.
For example Deal 2 with a value of 300,000 will earn 15%. So its, comm should be 300,000*15%=45,000
In addition a deal with a value of 1,500,001 are excluded from the accumulation level and use the normal tiers

 

[Aladin Akyurek]

See if this link helps:

https://www.mrexcel.com/forum/excel-questions/357834-tiered-commission-formula.html