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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
Subtotals and counta I think
Replies
1
Views
553
t0ny84
T
G
  • Solved
Split commission
2 3
Replies
20
Views
827
Sufiyan97
Sufiyan97
G
  • Question
Commission Tracker
Replies
2
Views
293
Greysoul
G
H
  • Question
Sales commission spreadsheet with variable commission rates
2
Replies
14
Views
13K
Dave Patton
D
JohnnyB
  • Question
Conditional Summing
Replies
2
Views
433
JohnnyB
JohnnyB

Forum statistics

Threads
1,215,137
Messages
6,123,253
Members
449,093
Latest member
Vincent Khandagale

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