Having trouble calculating commission which commissions portions of a sale differently

New Contributor

Hey,

 

Trying I am having trouble calculating commission which commissions portions of a sale differently. For example, if you have a sale that is 9000 USD. The first 3500 gives a commission of 4%, the 3500 after it gives a commission of 4.5%, and anything after that is commissioned 5%. Can anyone show me, while utilizing the vlookup function, how to do this? 

TIA

2 Replies

@Marobeast678 

Create a small lookup list as in the screenshot:S2037.png

Column I shows the increment from each tier to the next.

With a sales amount in A2, the commission is

=SUMPRODUCT((A2>$G$2:$G$4)*(A2-$G$2:$G$4),$I$2:$I$4)

@Hans Vogelaar Thanks for the help man, I tried it, it worked very nicely.