sunnybrook
New Member
- Joined
- Aug 3, 2010
- Messages
- 12
I have a commision pay scale where the 1st four units sold are not to be paid to the sales person.
Between 5 - 15 units sold, 3% commission is paid
Between 16 - 25 units sold, 4% commission is paid
Between 26 - 40 units sold, 5% commission is paid
Between 41 - 52 units sold, 6% commission is paid
Also, commission for each level attained is retroactive to the 5th unit sold.
When 53 units are sold, commission is paid from the first unit and increases to 7.5%
My sheet looks like this:
I6 = # of units sold
J6 = Price
K6 = Commission Paid
In K6, my formula looks like this:
=IF($I$101<5,J6*0,IF($I$101<16,J6*3%,IF($I$101<26,J6*4%,IF($I$101<41,J6*5%,IF($I$101<53,J6*6%,IF($I$101>=53,J6*7.5%))))))
I don't know how to sum the total commission paid in K101 because they will also be entering other products sold at different rates in different columns, so some cells will be empty in column K. Also, how would I sum column K but exclude adding the first 4 units sold?
Between 5 - 15 units sold, 3% commission is paid
Between 16 - 25 units sold, 4% commission is paid
Between 26 - 40 units sold, 5% commission is paid
Between 41 - 52 units sold, 6% commission is paid
Also, commission for each level attained is retroactive to the 5th unit sold.
When 53 units are sold, commission is paid from the first unit and increases to 7.5%
My sheet looks like this:
I6 = # of units sold
J6 = Price
K6 = Commission Paid
In K6, my formula looks like this:
=IF($I$101<5,J6*0,IF($I$101<16,J6*3%,IF($I$101<26,J6*4%,IF($I$101<41,J6*5%,IF($I$101<53,J6*6%,IF($I$101>=53,J6*7.5%))))))
I don't know how to sum the total commission paid in K101 because they will also be entering other products sold at different rates in different columns, so some cells will be empty in column K. Also, how would I sum column K but exclude adding the first 4 units sold?
