Calculating Commissions Part 2 (tough one!)

[msk7777]

Since my boss changed the variables I am really at a loss here on how to attempt to automate our commissions. Here is a sample of the spreadsheet that our recruiters use to enter their commissions. When they sign up someone with specific plans they just x-out the cell under the correct column. In the past we have just added up the totals manually. But now with growth there are too many recruiters to do this in a timely manner. I need to have Column S first look at Column D and determine if it is a Hard or Easy state (I already have them Named and defined). See here>>>

cOMMISSION PROJECT.xls
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	State	County	Neg. Sched.	Last Name	First Name	DPM Name	GD/SP	Date Rcvd	CPPO	CPPS	PO	PS	C5	HP	HO	Commission
2	CA			michael	King				X	X	X	X	X
3	CA			michael	King				X	X
4	CA			michael	King				X
5	CA			michael	King				X	X	X	X	X
6	MT			michael	King				X	X
7	MT			michael	King					X		X	X
May 26-Jun 1st

Then I need it to match the same X'd-out plans that I have defined on a different tab (if needed I could place it on the same worksheet) in Columns C-I and take the assigned commission from either Column J or K (these columns go down to line 99).

[HTML removed by admin]

 

[msk7777]

Option 2

Ok here is another option that I know is a lot easier to work with than my first, instead of having 98 different plan combinations (which is our current commission structure) I have asked that next year we change to a different structure to work with so there are less variables. On the same sheet I added the following where Column S can grab the correct correct commission from. If this is easier and anyone can help me with a formula that would match the correct commission total, that would help me with my proposal. I believe that with the above current commission structure there are too many variables for Excel to automate this process. Also, if anyone has a suggestion to format things differently so that it will work then feel free.

cOMMISSION PROJECT.xls
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	State	County	Neg.Sched.	LastName	FirstName	DPMName	GD/SP	DateRcvd	CPPO	CPPS	PO	PS	C5	HP	HO	Commission			CPPO	CPPS	PO	PS	C5	HP	HO
2	CA			michael	King				X	X	X	X	X					EASY	$3.50	$3.50	$4.50	$4.50	$9.00	$2.50	$2.50
3	CA			michael	King				X	X
4	CA			michael	King				X									HARD	$5.50	$5.50	$6.50	$6.50	$11.00	$2.50	$2.50
5	CA			michael	King				X	X	X	X	X
6	MT			michael	King				X	X
May 26-Jun 1st

 

[Von Pookie]

See:
http://www.mrexcel.com/board2/viewtopic.php?t=234325