Splitting Commission IF

G

greekexcel

New Member
Joined
Oct 31, 2022
Messages
32
Office Version
  1. 2019
Platform
  1. 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)
DEMO FOR VBA1.xlsx
ABCDEFGHIJ
120%15%20%10%
2CP DATEVESSELSQMAREAAMOUNTSALES REPKEVINJOHNROBERTBILL
325/2/2022PENTHOUSE355CENTRE$ 29,673.00KEVIN$ 5,934.60$ -$ -$ -
426/2/2022LOFT230SOUTH$ 28,032.00JOHN$ -$ 4,204.80$ -$ -
527/2/2022MANSION147NORTH$ 44,865.00BILL$ -$ -$ -$ 4,486.50
628/2/2022LOFT194NORTH$ 48,492.00KEVIN & JOHN$ -$ -$ -$ -
71/3/2022MANSION88SOUTH$ 41,831.00BILL &KEVIN$ -$ -$ -$ -
82/3/2022HOUSE78CENTRE$ 59,924.00ROBERT$ -$ -$ 11,984.80$ -
93/3/2022MANSION84CENTRE$ 64,867.00BILL & JOHN & ROBERT$ -$ -$ -$ -
104/3/2022PENTHOUSE177SOUTH$ 72,451.00ROBERT & KEVIN$ -$ -$ -$ -
115/3/2022LOFT113NORTH$ 39,103.00KEVIN & JOHN$ -$ -$ -$ -
126/3/2022LOFT347SOUTH$ 45,335.00KEVIN$ 9,067.00$ -$ -$ -
137/3/2022MANSION173CENTRE$ 47,890.00BILL &KEVIN$ -$ -$ -$ -
148/3/2022HOUSE163SOUTH$ 78,916.00ROBERT & KEVIN$ -$ -$ -$ -
159/3/2022HOUSE100NORTH$ 36,550.00ROBERT & KEVIN$ -$ -$ -$ -
1610/3/2022PENTHOUSE51WEST$ 60,594.00JOHN & BILL$ -$ -$ -$ -
1711/3/2022LOFT375WEST$ 28,861.00BILL & KEVIN$ -$ -$ -$ -
1812/3/2022LOFT279NORTH$ 74,130.00KEVIN & JOHN$ -$ -$ -$ -
1913/3/2022MANSION330EAST$ 58,979.00BILL &KEVIN$ -$ -$ -$ -
2014/3/2022PENTHOUSE84SOUTH$ 72,012.00JOHN$ -$ 10,801.80$ -$ -
2115/3/2022LOFT164EAST$ 18,166.00ROBERT & BILL$ -$ -$ -$ -
2215/12/2022HOUSE212WEST$ 46,311.00ROBERT & JOHN & BILL$ -$ -$ -$ -
Sheet1
Cell Formulas
RangeFormula
G3:G22G3=IF([@[SALES REP]]=Table1[[#Headers],[KEVIN]],[@AMOUNT]*$G$1,0)
H3:H22H3=IF([@[SALES REP]]=Table1[[#Headers],[JOHN]],[@AMOUNT]*$H$1,0)
I3:I22I3=IF([@[SALES REP]]=Table1[[#Headers],[ROBERT]],[@AMOUNT]*$I$1,0)
J3:J22J3=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.
 
Sort by date Sort by votes
Peter_SSs said:
It isn't according to your answers to the questions below where you said Bill gets 10% and the other person gets 15%. That is 25%, not 30%.
Click to expand...
Peter i am sorry you are absolutely right!
The standard collaboration commission is 30% in
greekexcel said:
The only exemption is "BILL" whoever makes a collaboration with "Bill" i want "Bill" to earn 10% and the other rep 15%.
Click to expand...
In case someone collaborates with "Bill" the collaboration commissions total is 25%
10% for Bill and 15% for the other salesrep.

Thanks again for your help.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Let's see how this goes then

greekexcel.xlsm
ABCDEFGHIJ
10.20.150.20.1
2CP DATEVESSELSQMAREAAMOUNTSALES REPKEVINJOHNROBERTBILL
344617PENTHOUSE355CENTRE$ 29,673.00KEVIN$ 5,934.60$ -$ -$ -
444618LOFT230SOUTH$ 28,032.00JOHN$ -$ 4,204.80$ -$ -
544619MANSION147NORTH$ 44,865.00BILL$ -$ -$ -$ 4,486.50
644620LOFT194NORTH$ 48,492.00KEVIN & JOHN$ 7,273.80$ 7,273.80$ -$ -
744621MANSION88SOUTH$ 41,831.00BILL & KEVIN$ 6,274.65$ -$ -$ 4,183.10
844622HOUSE78CENTRE$ 59,924.00ROBERT$ -$ -$ 11,984.80$ -
93/03/2022MANSION84CENTRE$ 64,867.00BILL & JOHN & ROBERT$ -$ 6,486.70$ 6,486.70$ 6,486.70
1044624PENTHOUSE177SOUTH$ 72,451.00ROBERT & KEVIN$ 10,867.65$ -$ 10,867.65$ -
1144625LOFT113NORTH$ 39,103.00KEVIN & JOHN$ 5,865.45$ 5,865.45$ -$ -
1244626LOFT347SOUTH$ 45,335.00KEVIN$ 9,067.00$ -$ -$ -
1344627MANSION173CENTRE$ 47,890.00BILL & KEVIN$ 7,183.50$ -$ -$ 4,789.00
1444628HOUSE163SOUTH$ 78,916.00ROBERT & KEVIN$ 11,837.40$ -$ 11,837.40$ -
1544629HOUSE100NORTH$ 36,550.00ROBERT & KEVIN$ 5,482.50$ -$ 5,482.50$ -
1644630PENTHOUSE51WEST$ 60,594.00JOHN & BILL$ -$ 9,089.10$ -$ 6,059.40
1744631LOFT375WEST$ 28,861.00BILL & KEVIN$ 4,329.15$ -$ -$ 2,886.10
1844632LOFT279NORTH$ 74,130.00KEVIN & JOHN$ 11,119.50$ 11,119.50$ -$ -
1944633MANSION330EAST$ 58,979.00BILL & KEVIN$ 8,846.85$ -$ -$ 5,897.90
2044634PENTHOUSE84SOUTH$ 72,012.00JOHN$ -$ 10,801.80$ -$ -
2144635LOFT164EAST$ 18,166.00ROBERT & BILL$ -$ -$ 2,724.90$ 1,816.60
2244910HOUSE212WEST$ 46,311.00ROBERT & JOHN & BILL$ -$ 4,631.10$ 4,631.10$ 4,631.10
Sheet1
Cell Formulas
RangeFormula
G3:I22G3=IF(ISNUMBER(SEARCH(" "&G$2&" "," "&$F3&" ")),IF(LEN($F3)-LEN(SUBSTITUTE($F3,"&",""))+1=1,$E3*G$1,$E3*0.3/(LEN($F3)-LEN(SUBSTITUTE($F3,"&",""))+1)),0)
J3:J22J3=IF(ISNUMBER(SEARCH(" "&J$2&" "," "&$F3&" ")),$E3*J$1,0)
 
Upvote 0
Solution
Peter_SSs said:
Let's see how this goes then

greekexcel.xlsm
ABCDEFGHIJ
10.20.150.20.1
2CP DATEVESSELSQMAREAAMOUNTSALES REPKEVINJOHNROBERTBILL
344617PENTHOUSE355CENTRE$ 29,673.00KEVIN$ 5,934.60$ -$ -$ -
444618LOFT230SOUTH$ 28,032.00JOHN$ -$ 4,204.80$ -$ -
544619MANSION147NORTH$ 44,865.00BILL$ -$ -$ -$ 4,486.50
644620LOFT194NORTH$ 48,492.00KEVIN & JOHN$ 7,273.80$ 7,273.80$ -$ -
744621MANSION88SOUTH$ 41,831.00BILL & KEVIN$ 6,274.65$ -$ -$ 4,183.10
844622HOUSE78CENTRE$ 59,924.00ROBERT$ -$ -$ 11,984.80$ -
93/03/2022MANSION84CENTRE$ 64,867.00BILL & JOHN & ROBERT$ -$ 6,486.70$ 6,486.70$ 6,486.70
1044624PENTHOUSE177SOUTH$ 72,451.00ROBERT & KEVIN$ 10,867.65$ -$ 10,867.65$ -
1144625LOFT113NORTH$ 39,103.00KEVIN & JOHN$ 5,865.45$ 5,865.45$ -$ -
1244626LOFT347SOUTH$ 45,335.00KEVIN$ 9,067.00$ -$ -$ -
1344627MANSION173CENTRE$ 47,890.00BILL & KEVIN$ 7,183.50$ -$ -$ 4,789.00
1444628HOUSE163SOUTH$ 78,916.00ROBERT & KEVIN$ 11,837.40$ -$ 11,837.40$ -
1544629HOUSE100NORTH$ 36,550.00ROBERT & KEVIN$ 5,482.50$ -$ 5,482.50$ -
1644630PENTHOUSE51WEST$ 60,594.00JOHN & BILL$ -$ 9,089.10$ -$ 6,059.40
1744631LOFT375WEST$ 28,861.00BILL & KEVIN$ 4,329.15$ -$ -$ 2,886.10
1844632LOFT279NORTH$ 74,130.00KEVIN & JOHN$ 11,119.50$ 11,119.50$ -$ -
1944633MANSION330EAST$ 58,979.00BILL & KEVIN$ 8,846.85$ -$ -$ 5,897.90
2044634PENTHOUSE84SOUTH$ 72,012.00JOHN$ -$ 10,801.80$ -$ -
2144635LOFT164EAST$ 18,166.00ROBERT & BILL$ -$ -$ 2,724.90$ 1,816.60
2244910HOUSE212WEST$ 46,311.00ROBERT & JOHN & BILL$ -$ 4,631.10$ 4,631.10$ 4,631.10
Sheet1
Cell Formulas
RangeFormula
G3:I22G3=IF(ISNUMBER(SEARCH(" "&G$2&" "," "&$F3&" ")),IF(LEN($F3)-LEN(SUBSTITUTE($F3,"&",""))+1=1,$E3*G$1,$E3*0.3/(LEN($F3)-LEN(SUBSTITUTE($F3,"&",""))+1)),0)
J3:J22J3=IF(ISNUMBER(SEARCH(" "&J$2&" "," "&$F3&" ")),$E3*J$1,0)
Click to expand...
Peter it works thank u very much.
However i presented this to my manager and he informed that BILL's commission from now will be 35% and not 10%🤦‍♂️
So the collaboration total percentage with Bill will be 45%
I changed the value in cell J1 from 0.10 to 0.35 but it does not work correctly.

I think i troubled you too much thank u anyway!
 
Last edited:
Upvote 0
greekexcel said:
However i presented this to my manager and he informed that BILL's commission from now will be 35% and not 10%🤦‍♂️
So the collaboration total percentage with Bill will be 45%
Click to expand...
So, Bill + 1 other person: Bill = 35%, Other = 10%

What about row 9 in the sample?
Bill + 2 others: Bill = 35%, Other 1 = ??. Other 2 = ??
And does it make any difference if the other two are Kevin (normally 20%) & John (normally 15%) or are Kevin (normally 20%) & Robert (normally 20%)?
 
Upvote 0
Peter_SSs said:
So, Bill + 1 other person: Bill = 35%, Other = 10%

What about row 9 in the sample?
Bill + 2 others: Bill = 35%, Other 1 = ??. Other 2 = ??
And does it make any difference if the other two are Kevin (normally 20%) & John (normally 15%) or are Kevin (normally 20%) & Robert (normally 20%)?
Click to expand...
So my managers decided to restructure the whole commission scheme :/
DEMO FOR VBA1.xlsx
ABCDEFGHIJ
130%20%10%35%
2CP DATEVESSELSQMAREAAMOUNTSALES REPKEVINJOHNROBERTBILL
325/2/2022PENTHOUSE355CENTRE$ 29,673.00KEVIN$ 8,901.90$ -$ -
426/2/2022LOFT230SOUTH$ 28,032.00JOHN$ -$ 5,606.40$ -
527/2/2022MANSION147NORTH$ 44,865.00BILL$ -$ -$ -
628/2/2022LOFT194NORTH$ 48,492.00KEVIN & JOHN$ 7,273.80$ 7,273.80$ -
71/3/2022MANSION88SOUTH$ 41,831.00BILL &KEVIN$ -$ -$ -
82/3/2022HOUSE78CENTRE$ 59,924.00ROBERT$ -$ -$ 5,992.40
93/3/2022MANSION84CENTRE$ 64,867.00BILL & JOHN & ROBERT$ -$ 6,486.70$ 6,486.70
104/3/2022PENTHOUSE177SOUTH$ 72,451.00ROBERT & KEVIN$ 10,867.65$ -$ 7,245.10
115/3/2022LOFT113NORTH$ 39,103.00KEVIN & JOHN$ 5,865.45$ 5,865.45$ -
126/3/2022LOFT347SOUTH$ 45,335.00KEVIN$ 13,600.50$ -$ -
137/3/2022MANSION173CENTRE$ 47,890.00BILL &KEVIN$ -$ -$ -
148/3/2022HOUSE163SOUTH$ 78,916.00ROBERT & KEVIN$ 11,837.40$ -$ 7,891.60
159/3/2022HOUSE100NORTH$ 36,550.00ROBERT & KEVIN$ 5,482.50$ -$ 3,655.00
1610/3/2022PENTHOUSE51WEST$ 60,594.00JOHN & BILL$ -$ 9,089.10$ -
1711/3/2022LOFT375WEST$ 28,861.00BILL & KEVIN$ 4,329.15$ -$ -
1812/3/2022LOFT279NORTH$ 74,130.00KEVIN & JOHN$ 11,119.50$ 11,119.50$ -
1913/3/2022MANSION330EAST$ 58,979.00BILL &KEVIN$ -$ -$ -
2014/3/2022PENTHOUSE84SOUTH$ 72,012.00JOHN$ -$ 14,402.40$ -
2115/3/2022LOFT164EAST$ 18,166.00ROBERT & BILL$ -$ -$ 1,816.60
2215/12/2022HOUSE212WEST$ 46,311.00ROBERT & JOHN & BILL$ -$ 4,631.10$ 4,631.10
23
Sheet1
Cell Formulas
RangeFormula
G3:H22G3=IF(ISNUMBER(SEARCH(" "&G$2&" "," "&$F3&" ")),IF(LEN($F3)-LEN(SUBSTITUTE($F3,"&",""))+1=1,$E3*G$1,$E3*0.3/(LEN($F3)-LEN(SUBSTITUTE($F3,"&",""))+1)),0)
I3:I22I3=IF(ISNUMBER(SEARCH(" "&I$2&" "," "&$F3&" ")),$E3*I$1,0)


Above is the new structure. The formulas that you provided work perfectly as you can see except Bill's case
Peter_SSs said:
What about row 9 in the sample?
Click to expand...
Row 9 is fine

So, Bill + 1 other person: Bill = 35%, Other = 15%
And Robert + 1 other person: Robert = 10% , Other = 15%
Robert + Bill : Robert = 10% + Bill = 35%

In conclusion:
Bill will always get 35% both individually and in collaboration
Robert will always get 10% both individually and in collaboration

Kevin and John will get 15% each in collaboration even if they collaborate with Bill or Robert.

I think that the only formula that needs update is Bill's because his commission percentage increased.

Peter once again thank you very much!
 
Upvote 0
greekexcel said:
Bill will always get 35% both individually and in collaboration
Click to expand...
Then can you explain exactly what you mean by this?
greekexcel said:
my manager and he informed that BILL's commission from now will be 35% and not 10%🤦‍♂️
So the collaboration total percentage with Bill will be 45%
I changed the value in cell J1 from 0.10 to 0.35 but it does not work correctly.
Click to expand...
Put the previous formula for Bill back in, enter 0.35 in J1 and explain which results are incorrect, what they should be and why.
 
Upvote 1
You're welcome. Glad we got there in the end. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Subtotals and counta I think
Replies
1
Views
726
t0ny84
T
G
  • Solved
Combobox search dropdown triggering when editing other sheets
2
Replies
10
Views
821
Akuini
Akuini
JenniferMurphy
  • Question Question
Best way to populate a table with a macro
Replies
2
Views
505
JenniferMurphy
JenniferMurphy
F
  • Question Question
Calculate percentages of dollar amounts on a table that can adjust to more or less accounts.
Replies
0
Views
423
frkre05
F
G
  • Solved
Split commission
2 3
Replies
20
Views
1K
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top