Help With Formula To Calculate Commissions

smonczka

New Member
Joined
Nov 3, 2005
Messages
39
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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!
 
Upvote 0
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
ABCDE
1Rep1Rep2Rep3Rep4$amount
2BobTedPhil$300.00
3BobRalphTedMike$2,967.00
4PhilMikeTed$248.00
5RalphBob$5,464.00
6
7
8
9
10Bob#VALUE!
11Ted#VALUE!
12Phil#VALUE!
13Ralph#VALUE!
14Mike#VALUE!
Sheet1
 
Upvote 0
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.
 
Upvote 0
smonczka said:
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%?
 
Upvote 0
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.
 
Upvote 0
smonczka said:
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
ABCDE
1
2Rep1Rep2Rep3Rep4$amount
3BobTedPhil300
4BobRalphTedMike2967
5PhilMikeTed248
6RalphBob5464
7
8
9
10Bob8731
11Ted3515
12Phil548
13Ralph8431
14Mike3215
15
Sheet2


B10:

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

which is confirmed with control+shift+enter then copied down.
 
Upvote 0
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
ABCD
1Rep1 Rep2 Rep3 Rep4 $Amount
2Bob Ted Phil 300
3Ted Ralph Ted Mike 500
4Phil Bob Mike Ted 435
5Ralph Bob 198
6
7
8What I need is to come up with a formula that would give me the
9following based off the grid above
10
11Total $Amount
12Bob933
13Ted1235
14Phil735
15Ralph698
16Mike935
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,058
Messages
6,105,654
Members
447,974
Latest member
misspancake

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top