greekexcel
New Member
- Joined
- Oct 31, 2022
- Messages
- 32
- Office Version
- 2019
- Platform
- Windows
Hello everyone.
I have this table that calculates and splits the commission between sales reps.
Each salesrep has a different commission percentage(you can see it above his name)
This formula only works when the sales is done individually.
I want a formula that calculates the commission for each salesrep if the sale is done individually according to his percentage
But if it is done in collaboration with another salesrep split the commission between them at 15% each
How do i combine this 2 criteria together and inside a table not normal range.
Thank u in advance.
I have this table that calculates and splits the commission between sales reps.
Each salesrep has a different commission percentage(you can see it above his name)
| DEMO FOR VBA1.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | 20% | 15% | 20% | 10% | ||||||||
| 2 | CP DATE | VESSEL | SQM | AREA | AMOUNT | SALES REP | KEVIN | JOHN | ROBERT | BILL | ||
| 3 | 25/2/2022 | PENTHOUSE | 355 | CENTRE | $ 29,673.00 | KEVIN | $ 5,934.60 | $ - | $ - | $ - | ||
| 4 | 26/2/2022 | LOFT | 230 | SOUTH | $ 28,032.00 | JOHN | $ - | $ 4,204.80 | $ - | $ - | ||
| 5 | 27/2/2022 | MANSION | 147 | NORTH | $ 44,865.00 | BILL | $ - | $ - | $ - | $ 4,486.50 | ||
| 6 | 28/2/2022 | LOFT | 194 | NORTH | $ 48,492.00 | KEVIN & JOHN | $ - | $ - | $ - | $ - | ||
| 7 | 1/3/2022 | MANSION | 88 | SOUTH | $ 41,831.00 | BILL &KEVIN | $ - | $ - | $ - | $ - | ||
| 8 | 2/3/2022 | HOUSE | 78 | CENTRE | $ 59,924.00 | ROBERT | $ - | $ - | $ 11,984.80 | $ - | ||
| 9 | 3/3/2022 | MANSION | 84 | CENTRE | $ 64,867.00 | BILL & JOHN & ROBERT | $ - | $ - | $ - | $ - | ||
| 10 | 4/3/2022 | PENTHOUSE | 177 | SOUTH | $ 72,451.00 | ROBERT & KEVIN | $ - | $ - | $ - | $ - | ||
| 11 | 5/3/2022 | LOFT | 113 | NORTH | $ 39,103.00 | KEVIN & JOHN | $ - | $ - | $ - | $ - | ||
| 12 | 6/3/2022 | LOFT | 347 | SOUTH | $ 45,335.00 | KEVIN | $ 9,067.00 | $ - | $ - | $ - | ||
| 13 | 7/3/2022 | MANSION | 173 | CENTRE | $ 47,890.00 | BILL &KEVIN | $ - | $ - | $ - | $ - | ||
| 14 | 8/3/2022 | HOUSE | 163 | SOUTH | $ 78,916.00 | ROBERT & KEVIN | $ - | $ - | $ - | $ - | ||
| 15 | 9/3/2022 | HOUSE | 100 | NORTH | $ 36,550.00 | ROBERT & KEVIN | $ - | $ - | $ - | $ - | ||
| 16 | 10/3/2022 | PENTHOUSE | 51 | WEST | $ 60,594.00 | JOHN & BILL | $ - | $ - | $ - | $ - | ||
| 17 | 11/3/2022 | LOFT | 375 | WEST | $ 28,861.00 | BILL & KEVIN | $ - | $ - | $ - | $ - | ||
| 18 | 12/3/2022 | LOFT | 279 | NORTH | $ 74,130.00 | KEVIN & JOHN | $ - | $ - | $ - | $ - | ||
| 19 | 13/3/2022 | MANSION | 330 | EAST | $ 58,979.00 | BILL &KEVIN | $ - | $ - | $ - | $ - | ||
| 20 | 14/3/2022 | PENTHOUSE | 84 | SOUTH | $ 72,012.00 | JOHN | $ - | $ 10,801.80 | $ - | $ - | ||
| 21 | 15/3/2022 | LOFT | 164 | EAST | $ 18,166.00 | ROBERT & BILL | $ - | $ - | $ - | $ - | ||
| 22 | 15/12/2022 | HOUSE | 212 | WEST | $ 46,311.00 | ROBERT & JOHN & BILL | $ - | $ - | $ - | $ - | ||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G3:G22 | G3 | =IF([@[SALES REP]]=Table1[[#Headers],[KEVIN]],[@AMOUNT]*$G$1,0) |
| H3:H22 | H3 | =IF([@[SALES REP]]=Table1[[#Headers],[JOHN]],[@AMOUNT]*$H$1,0) |
| I3:I22 | I3 | =IF([@[SALES REP]]=Table1[[#Headers],[ROBERT]],[@AMOUNT]*$I$1,0) |
| J3:J22 | J3 | =IF([@[SALES REP]]=Table1[[#Headers],[BILL]],[@AMOUNT]*$J$1,0) |
This formula only works when the sales is done individually.
I want a formula that calculates the commission for each salesrep if the sale is done individually according to his percentage
But if it is done in collaboration with another salesrep split the commission between them at 15% each
How do i combine this 2 criteria together and inside a table not normal range.
Thank u in advance.
