# Help With Formula To Calculate Commissions

#### smonczka

##### New Member
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

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

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!

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

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

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.

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%?

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.

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.

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.

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.

Replies
2
Views
97
Replies
1
Views
99
Replies
1
Views
1K
Replies
3
Views
94
Replies
13
Views
1K

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.

### Which adblocker are you using?

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

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