Tiered Commission Formula

Copper Contributor

What would be the most efficient formula for calculating Tiered Commission?

So for sales of products 1~20, the commission would be $300 per product.

Then from 21~30, it would be $400 per,

31~40, $500 per,

so on and so forth.

For instance, if someone sold 30 products, the commission would be $10,000.

1 Reply

@hjkchoi 

I'd create a table with the thresholds and the increase in commission per item.

S2316.png

The formula in B2 is

=SUMPRODUCT((A2>$E$2:$E$19)*(A2-$E$2:$E$19),$G$2:$G$19)