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]

Why don't convert table to normal range and use the formula in post #2.

 

[greekexcel]

Why don't convert table to normal range and use the formula in post #2.

View attachment 83566

Hello.
Even when i use the formula in post #2 the calculation is wrong
for example in cell G6 it returns $14,547 which is 30% commission i want it to return 15% when there is 2 salesrep

 

[Sufiyan97]

Is it not correct?

Book2
	A	B	C	D	E	F	G	H	I	J
2	CP DATE	VESSEL	SQM	AREA	AMOUNT	SALES REP	KEVIN	JOHN	ROBERT	BILL
3	2/28/2022	LOFT	194	NORTH	14547	KEVIN & JOHN	2182.05	2182.05	0	0
Sheet1 (2)

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

 

[Sufiyan97]

It is not returning 15%??

Book2
	D	E	F	G	H	I	J	K	L
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.
Sheet4

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]

Is it not correct?

Book2
	A	B	C	D	E	F	G	H	I	J
2	CP DATE	VESSEL	SQM	AREA	AMOUNT	SALES REP	KEVIN	JOHN	ROBERT	BILL
3	2/28/2022	LOFT	194	NORTH	14547	KEVIN & JOHN	2182.05	2182.05	0	0
Sheet1 (2)

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

This is the result
also i have to replace commas with semicolon

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	$ 8,901.90
4	26/2/2022	LOFT	230	SOUTH	$ 28,032.00	JOHN	#DIV/0!
5	27/2/2022	MANSION	147	NORTH	$ 44,865.00	KEVIN	$ 13,459.50
6	28/2/2022	LOFT	194	NORTH	$ 48,492.00	KEVIN & JOHN	$ 14,547.60
7	1/3/2022	MANSION	88	SOUTH	$ 41,831.00	BILL &KEVIN	$ 12,549.30
8	2/3/2022	HOUSE	78	CENTRE	$ 59,924.00	ROBERT	#DIV/0!
9	3/3/2022	MANSION	84	CENTRE	$ 64,867.00	BILL & JOHN & ROBERT	#DIV/0!
10	4/3/2022	PENTHOUSE	177	SOUTH	$ 72,451.00	ROBERT & KEVIN	$ 21,735.30
11	5/3/2022	LOFT	113	NORTH	$ 39,103.00	KEVIN & JOHN	$ 11,730.90
12	6/3/2022	LOFT	347	SOUTH	$ 45,335.00	KEVIN	$ 13,600.50
13	7/3/2022	MANSION	173	CENTRE	$ 47,890.00	BILL &KEVIN	$ 14,367.00
14	8/3/2022	HOUSE	163	SOUTH	$ 78,916.00	ROBERT & KEVIN	$ 23,674.80
15	9/3/2022	HOUSE	100	NORTH	$ 36,550.00	ROBERT & KEVIN	$ 10,965.00
16	10/3/2022	PENTHOUSE	51	WEST	$ 60,594.00	JOHN & BILL	#DIV/0!
17	11/3/2022	LOFT	375	WEST	$ 28,861.00	BILL & KEVIN	$ 8,658.30
18	12/3/2022	LOFT	279	NORTH	$ 74,130.00	KEVIN & JOHN	$ 22,239.00
19	13/3/2022	MANSION	330	EAST	$ 58,979.00	BILL &KEVIN	$ 17,693.70
20	14/3/2022	PENTHOUSE	84	SOUTH	$ 72,012.00	JOHN	#DIV/0!
21	15/3/2022	LOFT	164	EAST	$ 18,166.00	ROBERT & BILL	#DIV/0!
22	15/12/2022	HOUSE	212	WEST	$ 46,311.00	ROBERT & JOHN & BILL	#DIV/0!
Sheet1

Cell Formulas
Range	Formula
G3:G22	G3	=E3*0.3*--ISNUMBER(SEARCH(G$2:J$2,$F3))/SUM(--ISNUMBER(SEARCH(G$2:J$2,$F3)))

 

[Sufiyan97]

Are you using Office 365?

If you seen my results, it splits based on number of person.

 

[Sufiyan97]

If you are not using office 365 then select cells G3 to J3 then press F2 and hit CTRL + SHIFT + ENTER

 

[greekexcel]

Are you using Office 365?

If you seen my results, it splits based on number of person.

I am using office 2019
your results are correct exactly what i want
Any ideas what is wrong with my sheet?
Thank you

 

[Sufiyan97]

Have you seen post #17?

 

[greekexcel]

Thank you very much Sufiyan97 it finally worked!!!!
You are a Life saver :D