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. 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

 

[Guest]

Smonczka:

You can only get a good answere if you post a good question.
Item #1
Did Bob Ted Phil split the 300.00 equally
or did they get 300 each
if not how was the 300 split?

Good Luck
TK

Smonczka: wrote

 

[Guest]

Take a look at Sumif funtion and see if that fits your need

 

[Smonczka]

TK they do not split the commission. All are paid commission on the
total 300. so if there are four persons on a $300 all four would
receave commisson for the full 300.

David thanks I will look up the SumIF fuction. Thanks.

Steve

 

[Guest]

Smonczka:

Are the reports sent to you on a spreedsheet with
the reps in columns or are they all lumped together.
If they are in spreedsheet form one name one cell
a solution is pretty strightforward if not you will
need to treat it as a string and search the string for
occurrances of the differant names.

Good Luck
TK

 

[Bob Phillips]

This should do it

=SUMPRODUCT(--((A2:A10="Bob")+(B2:B10="Bob")),E2:E10)

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Smonczka]

Bob I was unable to get that to work. Looking at the formlu I see what
you are trying to do and it should work. But I run into a #name error.


A B C D E
1 Rep1 Rep2 Rep3 Rep4 $amount
2 Bob Ted Mike Tom 300
3 Bob Ted Mike Tom 2967
4 Bob Ted Mike Tom 248
5 Bob Ted Mike Tom 5464
6
7
8
9 Bob #NAME? {=SUMPRODUCT(--((A2:A5="Bob")+(C2:C5="Bob")),E2:E5:E7E10)}
10 Bob #VALUE! {=SUM(IF($A$3:$D$6=A10,$E$3:$E$6))}

I also tried { =SUM(IF($A$3:$D$6=A10,$E$3:$E$6)) } but end up with a
#value error.

Steve

 

[Bob Phillips]

It's important to get the ranges correct, so I have to ask, what is
E2:E5:E7E10 supposed to refer to. That is invalid, and incomprehensible
(meaning I can't see what you are trying to do ). And why the braces,
SUMPRODUCT doesn't need them.

Try this exactly as given please and tell me what you get

=SUMPRODUCT(--((A2:A5="Bob")+(B2:B5="Bob")+(C2:C5="Bob")+(D25="Bob")),E2:E
5)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Smonczka]

Bob this was my error. when I copied the formula over I somehow added
E7E10 on to the formula. Thus causing the error. And the brakets were
only to show you what formula i was using.

I corected the formula and it is working perfectly. Thank you very
kindly for your help.

Steve

 

[Bob Phillips]

Good news Steve. Glad it worked.

--

HTH

RP
(remove nothere from the email address if mailing direct)