+ Reply to Thread
Results 1 to 10 of 10

Help With Formula To Calculate Commissions

  1. #1
    Smonczka
    Guest

    Help With Formula To Calculate Commissions

    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


  2. #2
    TK
    Guest

    RE: Help With Formula To Calculate Commissions

    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

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


  3. #3
    David
    Guest

    RE: Help With Formula To Calculate Commissions

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


    "Smonczka" wrote:

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


  4. #4
    Smonczka
    Guest

    Re: Help With Formula To Calculate Commissions

    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


  5. #5
    TK
    Guest

    Re: Help With Formula To Calculate Commissions

    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


    "Smonczka" wrote:

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


  6. #6
    Bob Phillips
    Guest

    Re: Help With Formula To Calculate Commissions

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  7. #7
    Smonczka
    Guest

    Re: Help With Formula To Calculate Commissions

    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


  8. #8
    Bob Phillips
    Guest

    Re: Help With Formula To Calculate Commissions

    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")+(D2:D5="Bob")),E2:E
    5)

    --

    HTH

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


    "Smonczka" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  9. #9
    Smonczka
    Guest

    Re: Help With Formula To Calculate Commissions

    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


  10. #10
    Bob Phillips
    Guest

    Re: Help With Formula To Calculate Commissions

    Good news Steve. Glad it worked.

    --

    HTH

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


    "Smonczka" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1