Calculate commission based on units sold or GP

[getafix40]

Our commission structure is they get paid 10% of GP up to 40k.
20% of GP from 40k upwards but starting from 20k. If this happens they get 10% for the first 20k.

So if he sells a total of 60k GP his commission will be:

10% of 20k=2000
20% of 40k=8000
Total=10000

The problem comes in that he either has to sell 4 units or 40k GP. So if he sells 4 units and total 35k he will get paid
10% of 20k = 2000
20% of 15k = 3000

I have a formula to work out the first part, where C20 is his total GP
=IF(C20<40000,10%*C20,(10%*19999)+(20%*(C20-19999)))

But how do I add the number of units condition?

The formula I use to retrieve the GP for each salesman is, where C1 is the salesmans name, AH range is the column where the sales are captured by salesman name and AE is the column is the GP.
=SUMIFS(StockSheet!$AH:$AH,StockSheet!$AH:$AH,">0",StockSheet!$AE:$AE,C1)

Hope this makes sense.

 

[Steph77]

=IF(OR(C20>39999,XX20>3.9),(10%*19999)+(20%*(C20-19999)),(10%*19999))

Change "XX" for whatever the relevant item sales number column is

 

[getafix40]

=IF(OR(C20>39999,XX20>3.9),(10%*19999)+(20%*(C20-19999)),(10%*19999))

Change "XX" for whatever the relevant item sales number column is

Thanks for the help, but your formula returns 10% of 19999 not of the total GP. EG salesman BOB has sold two units and his GP is R38k. He will get paid 10% of 38k not 19999.
If he sells 4 units totaling 38k, he will get paid 10% up to 19999 and 20% of the balance (18k)
If he sell 2 units and his GP is more than 40k, he will get paid 10% of 19999 and 20% of the balance (21k)

I am not sure how to change your formula accordingly

 

[Steph77]

In the last set of brackets change it to (10%*C20) - sorry I just copy/pasted badly...

 

[getafix40]

In the last set of brackets change it to (10%*C20) - sorry I just copy/pasted badly...

BAM - solved. Youre a star.

How do you guys know all this stuff.