Splitting Commission IF

[greekexcel]

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
	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.

 

[Peter_SSs]

But if it is done in collaboration with another salesrep split the commission between them at 15% each

So are you saying that for row 9 each of the 3 reps get 15% meaning a total of 45% commission is paid?

Do you have the TEXTSPLIT function? That is, if you start typing =TEXT in a cell does this option appear?

 

[greekexcel]

So are you saying that for row 9 each of the 3 reps get 15% meaning a total of 45% commission is paid?

Do you have the TEXTSPLIT function? That is, if you start typing =TEXT in a cell does this option appear?
View attachment 84145

Hello.
I dont have TEXTSPLIT function i am using excel 2019.
For row 9 and in any case that they are three sales reps the commission is 10% but that does not very frequently so i can manually input in the worst case scenario.
2 sales rep happens most frequently
Thank you

 

[Peter_SSs]

I dont have TEXTSPLIT function i am using excel 2019.

Does your profile need updating then as it says 365 and 2021 as well as 2019?

For row 9 and in any case that they are three sales reps the commission is 10%

2 reps = 15% each
3 reps = 10% each

Is it a fair assumption then that however many multiple reps there are, they share 30%?

 

[greekexcel]

Does your profile need updating then as it says 365 and 2021 as well as 2019?

I updated and now i have TEXTJOIN

2 reps = 15% each
3 reps = 10% each

Is it a fair assumption then that however many multiple reps there are, they share 30%?

That is correct but maybe Bill will still stay 10% at in collaboration as well.
Thank u in advance

 

[Sanjeev1976]

Try this in G3 and then copied down and across different agents :

=IF(ISNUMBER(SEARCH(G$2,$F3)),$E3/COUNTA(TEXTSPLIT($F3,"&"))*G$1,0)

I have considered commission on equal split of amount amongst the agents

 

[greekexcel]

Try this in G3 and then copied down and across different agents :

=IF(ISNUMBER(SEARCH(G$2,$F3)),$E3/COUNTA(TEXTSPLIT($F3,"&"))*G$1,0)

I have considered commission on equal split of amount amongst the agents

First of all thank u for your response
I dont think the calculation works correct.
For example Row 6 Kevin's commission is 9698$ which is 20% not 15%

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	$ 9,698.40	$ 7,273.80	$ -	$ -
7	1/3/2022	MANSION	88	SOUTH	$ 41,831.00	BILL &KEVIN	$ 8,366.20	$ -	$ -	$ 4,183.10
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	$ -	$ 9,730.05	$ 12,973.40	$ 6,486.70
10	4/3/2022	PENTHOUSE	177	SOUTH	$ 72,451.00	ROBERT & KEVIN	$ 14,490.20	$ -	$ 14,490.20	$ -
11	5/3/2022	LOFT	113	NORTH	$ 39,103.00	KEVIN & JOHN	$ 7,820.60	$ 5,865.45	$ -	$ -
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	$ 9,578.00	$ -	$ -	$ 4,789.00
14	8/3/2022	HOUSE	163	SOUTH	$ 78,916.00	ROBERT & KEVIN	$ 15,783.20	$ -	$ 15,783.20	$ -
15	9/3/2022	HOUSE	100	NORTH	$ 36,550.00	ROBERT & KEVIN	$ 7,310.00	$ -	$ 7,310.00	$ -
16	10/3/2022	PENTHOUSE	51	WEST	$ 60,594.00	JOHN & BILL	$ -	$ 9,089.10	$ -	$ 6,059.40
17	11/3/2022	LOFT	375	WEST	$ 28,861.00	BILL & KEVIN	$ 5,772.20	$ -	$ -	$ 2,886.10
18	12/3/2022	LOFT	279	NORTH	$ 74,130.00	KEVIN & JOHN	$ 14,826.00	$ 11,119.50	$ -	$ -
19	13/3/2022	MANSION	330	EAST	$ 58,979.00	BILL &KEVIN	$ 11,795.80	$ -	$ -	$ 5,897.90
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	$ -	$ -	$ 3,633.20	$ 1,816.60
22	15/12/2022	HOUSE	212	WEST	$ 46,311.00	ROBERT & JOHN & BILL	$ -	$ 6,946.65	$ 9,262.20	$ 4,631.10
Sheet1

Cell Formulas
Range	Formula
G3:J22	G3	=IF(ISNUMBER(SEARCH(G$2,$F3)),$E3/COUNTA(TEXTSPLIT($F3,"&"))*G$1,0)

 

[Sanjeev1976]

Try this instead :

=IF(ISNUMBER(SEARCH(G$2,$F3)),IF(AND(COUNTA(TEXTSPLIT($F3,"&"))>1,G$2<>"BILL"),$E3*15%,IF(AND(COUNTA(TEXTSPLIT($F3,"&"))>1,G$2="BILL"),$E3*$J$1,IF(COUNTA(TEXTSPLIT($F3,"&"))=1,$E3*G$1,0))),0)

 

[greekexcel]

=IF(ISNUMBER(SEARCH(G$2,$F3)),IF(COUNTA(TEXTSPLIT($F3,"&"))>1,$E3*15%,IF(COUNTA(TEXTSPLIT($F3,"&"))=1,$E3*G$1,0)),0)

Unfortunately still the same

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	$ 9,698.40	$ 7,273.80	$ -	$ -
7	1/3/2022	MANSION	88	SOUTH	$ 41,831.00	BILL &KEVIN	$ 8,366.20	$ -	$ -	$ 4,183.10
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	$ -	$ 9,730.05	$ 12,973.40	$ 6,486.70
10	4/3/2022	PENTHOUSE	177	SOUTH	$ 72,451.00	ROBERT & KEVIN	$ 14,490.20	$ -	$ 14,490.20	$ -
11	5/3/2022	LOFT	113	NORTH	$ 39,103.00	KEVIN & JOHN	$ 7,820.60	$ 5,865.45	$ -	$ -
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	$ 9,578.00	$ -	$ -	$ 4,789.00
14	8/3/2022	HOUSE	163	SOUTH	$ 78,916.00	ROBERT & KEVIN	$ 15,783.20	$ -	$ 15,783.20	$ -
15	9/3/2022	HOUSE	100	NORTH	$ 36,550.00	ROBERT & KEVIN	$ 7,310.00	$ -	$ 7,310.00	$ -
16	10/3/2022	PENTHOUSE	51	WEST	$ 60,594.00	JOHN & BILL	$ -	$ 9,089.10	$ -	$ 6,059.40
17	11/3/2022	LOFT	375	WEST	$ 28,861.00	BILL & KEVIN	$ 5,772.20	$ -	$ -	$ 2,886.10
18	12/3/2022	LOFT	279	NORTH	$ 74,130.00	KEVIN & JOHN	$ 14,826.00	$ 11,119.50	$ -	$ -
19	13/3/2022	MANSION	330	EAST	$ 58,979.00	BILL &KEVIN	$ 11,795.80	$ -	$ -	$ 5,897.90
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	$ -	$ -	$ 3,633.20	$ 1,816.60
22	15/12/2022	HOUSE	212	WEST	$ 46,311.00	ROBERT & JOHN & BILL	$ -	$ 6,946.65	$ 9,262.20	$ 4,631.10
Sheet1

Cell Formulas
Range	Formula
G3:G22	G3	=IF(ISNUMBER(SEARCH(G$2,$F3)),IF(COUNTA(TEXTSPLIT($F3,"&"))>1,$E3*15%,IF(COUNTA(TEXTSPLIT($F3,"&"))=1,$E3*G$1,0)),0)
H3:J22	H3	=IF(ISNUMBER(SEARCH(H$2,$F3)),$E3/COUNTA(TEXTSPLIT($F3,"&"))*H$1,0)

 

[Sanjeev1976]

Temp.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/02/2022	PENTHOUSE	355	CENTRE	$29,673.00	KEVIN	$ 5,934.60	$ -	$ -	$ -
4	26/02/2022	LOFT	230	SOUTH	$28,032.00	JOHN	$ -	$ 4,204.80	$ -	$ -
5	27/02/2022	MANSION	147	NORTH	$44,865.00	BILL	$ -	$ -	$ -	$4,486.50
6	28/02/2022	LOFT	194	NORTH	$48,492.00	KEVIN & JOHN	$ 7,273.80	$ 7,273.80	$ -	$ -
7	01/03/2022	MANSION	88	SOUTH	$41,831.00	BILL &KEVIN	$ 6,274.65	$ -	$ -	$4,183.10
8	02/03/2022	HOUSE	78	CENTRE	$59,924.00	ROBERT	$ -	$ -	$11,984.80	$ -
9	03/03/2022	MANSION	84	CENTRE	$64,867.00	BILL & JOHN & ROBERT	$ -	$ 9,730.05	$ 9,730.05	$6,486.70
10	04/03/2022	PENTHOUSE	177	SOUTH	$72,451.00	ROBERT & KEVIN	$10,867.65	$ -	$10,867.65	$ -
11	05/03/2022	LOFT	113	NORTH	$39,103.00	KEVIN & JOHN	$ 5,865.45	$ 5,865.45	$ -	$ -
12	06/03/2022	LOFT	347	SOUTH	$45,335.00	KEVIN	$ 9,067.00	$ -	$ -	$ -
13	07/03/2022	MANSION	173	CENTRE	$47,890.00	BILL &KEVIN	$ 7,183.50	$ -	$ -	$4,789.00
14	08/03/2022	HOUSE	163	SOUTH	$78,916.00	ROBERT & KEVIN	$11,837.40	$ -	$11,837.40	$ -
15	09/03/2022	HOUSE	100	NORTH	$36,550.00	ROBERT & KEVIN	$ 5,482.50	$ -	$ 5,482.50	$ -
16	10/03/2022	PENTHOUSE	51	WEST	$60,594.00	JOHN & BILL	$ -	$ 9,089.10	$ -	$6,059.40
17	11/03/2022	LOFT	375	WEST	$28,861.00	BILL & KEVIN	$ 4,329.15	$ -	$ -	$2,886.10
18	12/03/2022	LOFT	279	NORTH	$74,130.00	KEVIN & JOHN	$11,119.50	$11,119.50	$ -	$ -
19	13/03/2022	MANSION	330	EAST	$58,979.00	BILL &KEVIN	$ 8,846.85	$ -	$ -	$5,897.90
20	14/03/2022	PENTHOUSE	84	SOUTH	$72,012.00	JOHN	$ -	$10,801.80	$ -	$ -
21	15/03/2022	LOFT	164	EAST	$18,166.00	ROBERT & BILL	$ -	$ -	$ 2,724.90	$1,816.60
22	15/12/2022	HOUSE	212	WEST	$46,311.00	ROBERT & JOHN & BILL	$ -	$ 6,946.65	$ 6,946.65	$4,631.10
Sheet1

Cell Formulas
Range	Formula
G3:J22	G3	=IF(ISNUMBER(SEARCH(G$2,$F3)),IF(AND(COUNTA(TEXTSPLIT($F3,"&"))>1,G$2<>"BILL"),$E3*15%,IF(AND(COUNTA(TEXTSPLIT($F3,"&"))>1,G$2="BILL"),$E3*$J$1,IF(COUNTA(TEXTSPLIT($F3,"&"))=1,$E3*G$1,0))),0)