# Thread: Help with a solution for calculating incentives

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

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

3. 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. I was actually hoping to have something like this that I found on this site earlier.....

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

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

8. ## 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. Hi George