# Calculate commission based on units sold or GP

#### getafix40

##### New Member
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.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Steph77

##### Board Regular
=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

##### New Member
=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

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

#### getafix40

##### New Member
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.

Replies
1
Views
219
Replies
11
Views
437
Replies
11
Views
2K
Replies
9
Views
404
Replies
3
Views
241

1,191,276
Messages
5,985,710
Members
439,975
Latest member
eelyn

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back