Help With Formula To Calculate Commissions

[smonczka]

I am trying to calculate commissions for our sales rep. Every month a
report is run that lists each invoice, the total for that invoice and
the sales reps associated with the sale. The problem is there may be
up to four reps per invoice and commission is paid on the full sales. So if Bob and three other reps made a $400 sale all receave commission on the full $400. The report I get looks something like the
grid below...


# Rep1 Rep2 Rep3 Rep4 $Amount
1 Bob Ted Phil 300.00
2 Ted Ralph Ted Mike 500.00
3 Phil Bob Mike Ted 435.00
4 Ralph Bob 198.00


What I need is to come up with a formula that would give me the
following based off the grid above...


Bob Total $Amount
Ted Total $Amount
Phil Total $Amount
Ralph Total $Amount
Mike Total $Amount


I was looking at VLookup but could not make it fit the problem. Any
ideas would be helpful.


As always thanks for any help you can give,
Steve

 

[Domenic]

Assumptions:

A1:E1 contains Rep, Rep2, Rep3, Rep4, and Amount

A2:E5 contains your data

A10:A14 contains your list of reps

Commission rate is 10%


Formula:

If each rep receives a commission based on the full sales amount...

B10, copied down:

=SUMPRODUCT(--(MMULT(--($A$2:$D$5=A10),TRANSPOSE(COLUMN($A$2:$D$5)^0))>0),$E$2:$E$5)*10%

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

If each rep receives a commision based on his share of the sales amount...

B10, copied down:

=SUMPRODUCT(--(MMULT(--($A$2:$D$5=A10),TRANSPOSE(COLUMN($A$2:$D$5)^0))>0),$E$2:$E$5/MMULT(--($A$2:$D$5<>""),TRANSPOSE(COLUMN($A$2:$D$5)^0)))*10%

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[smonczka]

I tried your example but was unable to get anything other than #Value!
I have never used any of the functions you show in your example so so I have no way of knowing just what I may have wrong... Here is a copy of what I did.

Book1
	A	B	C	D	E
1	Rep1	Rep2	Rep3	Rep4	$amount
2	Bob	Ted	Phil		$300.00
3	Bob	Ralph	Ted	Mike	$2,967.00
4	Phil	Mike	Ted		$248.00
5	Ralph	Bob			$5,464.00
6
7
8
9
10	Bob	#VALUE!
11	Ted	#VALUE!
12	Phil	#VALUE!
13	Ralph	#VALUE!
14	Mike	#VALUE!
Sheet1

 

[smonczka]

sorry about that, i'm new to how this works.

 

[starl]

No problem, smonczka - I fixed it for you. next time, you don't need to put the code within the code brackets - you just paste it like a normal message.

 

[Aladin Akyurek]

I tried your example but was unable to get anything other than #Value!
I have never used any of the functions you show in your example so so I have no way of knowing just what I may have wrong... Here is a copy of what I did...

Is the commission always 10%?

 

[smonczka]

I'm sorry to all concerned I worded my question incorectly. I am NOT trying to calculate the comission totals. Just the sales totals by individual. Once i come up with a the totals sales for each employee then I have formulas that figure out the commissions for me. that's the easy part. what I am trying to figure out is how do i total up what each person sold.

 

[Aladin Akyurek]

I'm sorry to all concerned I worded my question incorectly. I am NOT trying to calculate the comission totals. Just the sales totals by individual. Once i come up with a the totals sales for each employee then I have formulas that figure out the commissions for me. that's the easy part. what I am trying to figure out is how do i total up what each person sold.

Book2
	A	B	C	D	E
1
2	Rep1	Rep2	Rep3	Rep4	$amount
3	Bob	Ted	Phil		300
4	Bob	Ralph	Ted	Mike	2967
5	Phil	Mike	Ted		248
6	Ralph	Bob			5464
7
8
9
10	Bob	8731
11	Ted	3515
12	Phil	548
13	Ralph	8431
14	Mike	3215
15
Sheet2

B10:

=SUM(IF($A$3:$D$6=A10,$E$3:$E$6))

which is confirmed with control+shift+enter then copied down.

 

[Yogi Anand]

Hi Smonczka:

I don't know how your data is laid out. In the following illustration I have assumed the amounts to be 3 digit numbers ...

Book1
	A	B	C	D
1	Rep1 Rep2 Rep3 Rep4 $Amount
2	Bob Ted Phil 300
3	Ted Ralph Ted Mike 500
4	Phil Bob Mike Ted 435
5	Ralph Bob 198
6
7
8	What I need is to come up with a formula that would give me the
9	following based off the grid above
10
11	Total $Amount
12	Bob	933
13	Ted	1235
14	Phil	735
15	Ralph	698
16	Mike	935
17
Sheet4

If this works for you and the amounts could be of variable digits, the formula can be modified to suit. Post back if you need to discuss this further.

 

[smonczka]

Aladin Your approch is exactly what I am looking for but when I use your formulat I end up with a #Value error. I can see what the formula is suposed to be doing,

{=SUM(IF($A$3:$D$6=A10,$E$3:$E$6))}

if A3 to D6 is equel to what ever is in A10 then sum what ever is on those same rows in E3 to E6. It looks as if it should work but dosn't. Any ideas as to what I am doing incorectly.

Thank you.

Yogi, the data for each rep, the names and the sales amounts are all in separate columns. If i understand your solution corectly you are assuming that the invoice totals are on different rows but the rep information is all in the same cell.