commision on multiple invoices with sliding scale.

[scottymong]

We have a sales rep and they enter all their invoices at the end of
the month. We want pay out commisions based on each invoice value. So
we want to sum up invoices in a certain range and pay out x percetage
based on the sum. I can get the first 5% commission I just cant get
the 3% and 2% commission formula to work.

Here is what I could come up with so far:

Invoice value
Invoice 1 2000
invoice 2 95001
Invoice 3 1256
invoice 4 62000

Commission
5% for under 50,000= 162.8 =SUMIF($B$3:$B$6,"<=50000")*(0.05)

3% for >50,0001----<=90,000=? =SUMIF($B$3:$B$6,">50000 but <=90000)*.
03

2% for >90,001=?

I can work it out on my abucus, but cant get it to work in Excel
Thanks for any help on this

 

[JE McGimpsey]

Do you really pay a significantly lower total commission on $90,001
(i.e., 90001 * 2% = $1,800.02) than you do on $50,000 (i.e., 50000 * 5%
= $2,500)???

If so, then (aside from the fact that I'm glad I don't work for your
company) one way:

J1: =SUMIF($B$3:$B$6,"<=50000")*0.05
J2: =(SUMIF($B$3:$B$6,"<=90000") - SUMIF($B$3:$B$6,"<=50000"))*0.03
J3: =SUMIF($B$3:$B$6,">90000")*0.02

J2 can also be calculated as

=SUMPRODUCT(--($B$3:$B$6>50000),--($B$3:$B$6<=90000),$B$3:$B$6)*0.03

If your scale is actually progressive, e.g., with $50,001 getting 5% on
the first $50K, and 3% on the amount above $50K, see

http://www.mcgimpsey.com/excel/variablerate.html