Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with a solution for calculating incentives

    I am not a programmer but I am certain that what I am trying to do is doable. I have a spreadsheet that I laid out to calculate incentives for sales people selling used cars.

    The first page has all of the calculations in it and works fine. What I am trying to do is have a second sheet that allows you to type in the pay period and select a sales person and have it pull the totals for that person from the first page puts them on the second page for that pay period.

    I have looked at several solutions that people have here and tried to figure it out but, as I said, I am not a programmer.
    I would really appreciate any assistance I could get.

    Thanks,
    George
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Welcome to the lounge, George.

    See the attached. Is this what you need?

    The formula that was filled down is an array formula: CTRL+Shift+Enter
    Multiplying each test is an "and" condition: Condition1 and Condition2 and Condition3, etc.
    Then, summing.

    Kevin
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No, actually the idea is that each week the spreadsheet will be added to and with new sales. Then every two week I would like to simply change the dates for the pay period and have the totals for that date range totaled.

    What I really would like is to be able to enter the beginning and ending date for the pay period then show the car with the total for that sales person and total for the period. If that makes sense.

    The idea is that I don't want to have to do calculations every pay period but have buttons that can be clicked on to pull data based on date range for the pay period.

    Thanks,
    George

  4. #4
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was actually hoping to have something like this that I found on this site earlier.....
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    You can change the range of dates in the version I sent you, and add add'l data as well (down to row 2169).

    For what it's worth, your hidden column of calculations could be: =IF(ISBLANK(H6),0,100+10*(H6-1))
    Last edited by kweaver; 2013-03-31 at 16:25.

  6. #6
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi Eagle01

    SUMIFS is all you need for that in your file that you posted in post #1
    In C6 and copy down:

    =SUMIFS(Complete!$AE$6:$AE$100,Complete!$F$6:$F$10 0,$A6,Complete!$D$6:$D$100,">="&$A$2,Complete!$D$6 :$D$100,"<="&$C$2)

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi George

    Kevin is right in that SUMIFS can be used rather than array formulas.
    There are pros and cons with the choice.
    SUMIFS calc faster I believe.

    So, in my attached solution:
    I have defined some named ranges.
    I have added a 'horizontal clicker' to let you select previous pay periods without entering any dates.
    I have added a [Current Pay period] button to select current pay period.
    I have added a formula to also give you number of Units per person in the selected pay period.

    Finally, this uploaded file is of type .xlsm (110Kb)
    But if you save it as type .xlsb, it will reduce in size to 65Kb but still work the same with macros.
    (can't upload .xlsb files here yet).

    zeddy
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    Mar 2013
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Just what I was looking for.... But I have another Question

    Zeddy,

    That is exactly what I was looking for but I have another questions. Is there a way to, under each sales person put the list of addresses and upsells for each person so they could see exactly how the totals were calculated short of sorting the range and copying and pasting that information.

    Thanks,
    George

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi George

    What addresses????

    I am sure that what you are asking for could be done.
    It is easier for us to do this if you give an example file, and include a sheet showing an example of what you hope to get.

    zeddy

Posting Permissions

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