Commission Per Sale + Tiers

[TradieMike]

Hi Guys,

I was hoping you'd be able to help me with a formula to calculate monthly commissions for my team.

My team are paid per sale with the amount of commission increasing at different tiers.

0-5 sales = $0 per sale
6-55 sales = $25 per sale
56+ sales = $50 per sale

55 would equal to 100% of target and would result in $1,250 commision and then ever sale after that is worth $50 so for example if the achieve 56 sales then their total commission would be $1,300.

So what I'm looking for (if possible) is a formula that would allow me to just enter the amount of sales next to the agent (as show below) and it do that calculation for me.

Any help would be appreciated.

Cheers,
Mike

 

[TradieMike]

[B]AhoyNC[/B]'s formula is the one I went with as it was the most accurate. Thanks for checking in MARK858, I appreciate the feedback.

 

[Man237]

Hi,

Can someone explain how AhoyNC's formula works?

Thanks

 

[AhoyNC]

See this link.

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

 

[Man237]

Hi AhoyNC,

I'll check it out.

Thanks