Split commission

[greekexcel]

Hello i have this table
I want a formula that can split the commission when the sale is done by 2 more people.
For example when i input "KEVIN & JOHN" i want excel to split the commission 15% for KEVIN and 15% for JOHN and return their commission accordingly.
Thank u

 

[Sufiyan97]

Try

Book3
	C	D	E	F	G	H	I	J	K	L
1
2			Amount	Sales Rep	Kevin	John	Robert	Bill
3			29673	Kevin	8901.9	0	0	0
4			28032	John	0	8409.6	0	0
5			44865	Bill	0	0	0	13459.5
6			48492	Kevin & John	7273.8	7273.8	0	0
7			64867	Bill & John & Robert	0	6486.7	6486.7	6486.7
8			72451	Robert & Kevin	10867.65	0	10867.65	0
9			47890	Bill & Kevin	7183.5	0	0	7183.5
10
11
12				For example when i input "KEVIN & JOHN" i want excel to split the commission 15% for KEVIN and 15% for JOHN and return their commission accordingly.
13
Sheet1

Cell Formulas
Range	Formula
G3:J9	G3	=E3*0.3*--ISNUMBER(SEARCH(G$2:J$2,$F3))/SUM(--ISNUMBER(SEARCH(G$2:J$2,$F3)))
Dynamic array formulas.

 

[greekexcel]

Try

Book3
	C	D	E	F	G	H	I	J	K	L
1
2			Amount	Sales Rep	Kevin	John	Robert	Bill
3			29673	Kevin	8901.9	0	0	0
4			28032	John	0	8409.6	0	0
5			44865	Bill	0	0	0	13459.5
6			48492	Kevin & John	7273.8	7273.8	0	0
7			64867	Bill & John & Robert	0	6486.7	6486.7	6486.7
8			72451	Robert & Kevin	10867.65	0	10867.65	0
9			47890	Bill & Kevin	7183.5	0	0	7183.5
10
11
12				For example when i input "KEVIN & JOHN" i want excel to split the commission 15% for KEVIN and 15% for JOHN and return their commission accordingly.
13
Sheet1

Cell Formulas
Range	Formula
G3:J9	G3	=E3*0.3*--ISNUMBER(SEARCH(G$2:J$2,$F3))/SUM(--ISNUMBER(SEARCH(G$2:J$2,$F3)))
Dynamic array formulas.

Hello and Thank u for your response
Unfortunately i get the message "There is a problem with this formula"

 

[Sufiyan97]

in What version of excel you are using this?

 

[Fluff]

Unfortunately i get the message "There is a problem with this formula"

Try replacing the commas with semi-colons, if that is what you usually use.

 

[greekexcel]

I installed XL2BB but the buttons are greyed out.
I will try to find a solution with XL2BB and post a sample data
Thank u all!

 

[Fluff]

Did you try what I suggested?

 

[greekexcel]

Did you try what I suggested?

 

[greekexcel]

Good morning i tried but i was not workin properly.
I will post a sample data thank u

 

[greekexcel]

Hey everyone this is a sample data thank u in advance!

DEMO FOR VBA1.xlsx
	A	B	C	D	E	F	G	H	I	J
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)