Commissions based on Multiple Variables

[jonh]

Hi, our payroll dept., has asked if we can help them to calculate the commissions payable to 200+ salespersons based on the tables below. I used SUMPRODUCT in the past but with everyone on the same commission structure. Here, the structure differs by Location and the amounts billed. Thus a sales person in the North billing 155,000 will get the first 150,000 at 25.0% and the balance at 27.5%. Someone in the South billing the same amount would have all their billings at 25.0%

In addition, there are drop dead bonus (see table b below) for hitting certain values, so again someone in the North billing 275,000 would get a bonus of 4000, no bonus for billing less than 250,000 but maximum bonus is 10,000 for amounts in excess of 350,000. Here again Location & Billings determine the bonus payable.

As I said, I've tried using LOOKUP & SUMPRODUCT combinations but to no avail. I would greatly appreciate any assistance on the below. Payroll have laid out the s/sheet (see bottom of this message) with Salesperson in Col A; Cat. in Col B; Sales Amount in C; Commissions (based on Location & Billings) in Col D; and Drop dead bonus (based again on Location and billings) in Col E. Thus they want formulae in Cols D & E based on criteria in Column B (Cat. = Location Code) and C (Billings Generated).

Regards,
Jon


(a) Commissions
Cat. Desc. 25.0% 27.5% 30.0% 35.0% 40.0%
A North 150000 175000 200000 225000 250000
B South 160000 185000 210000 235000 260000
C East 140000 165000 190000 215000 240000
D West 110000 135000 160000 185000 210000
E London 200000 225000 250000 275000 300000

(b) Drop Dead Bonus
Sales Area/Sale Amounts
North 250000 300000 350000
South 200000 250000 300000
East 250000 300000 350000
West 200000 250000 300000
London 300000 350000 400000

Drop Dead Bonus Payable 4000 6000 10000



Salesperson; Cat.; Sales Amount; Commissions; Drop Dead Bonus;
A.N.Other1 A 155000
A.N.Other2 A 306000
A.N.Other3 C 115000
A.N.Other4 E 127500
A.N.Other5 A 75000