Forum Discussion
I need help with a Sumif formula
Hi Everyone
I have 20 sales reps and we are kicking off a new commissions structure, whereby the sales rep is rewarded for selling more than 70 subscriptions in a month. If they sell =< 70 subscriptions then they only receive their base pay.
For the life of me I cannot figure out how to create a formula to calculate this.....
Below is a example of what I am trying to achieve:
If a Sales Rep sells =<70 seats per month, then their commissions is $0
If a Sales Rep sells =<110 seats per month, then their commissions should be calculated at the full number of subscriptions or $6.00 X 110 or $660.00
| Start of Month # of Seats Sold | End of Month # of Seats Sold | Commissions Payable Per Seat |
| 0 | 70 | $ |
| 0 | 100 | $5.00 |
| 0 | 120 | $6.00 |
| 0 | 150 | $7.50 |
| 0 | 200 | $9.00 |
| 0 | >201 | $12.00 |
Help :-)
Hi Doug,
I think that this is what you looking for:
Please find the attached file.
Hope that helps
Haytham
Doug,
build a lookup table and use LOOKUP() to find the value.
=LOOKUP(D2,$A$1:$B$7)*D2
Number Commission 0 $0,00 110 $660,00 71 $5,00 101 $6,00 121 $7,50 151 $9,00 201 $12,00 Thank you Detlef, I really appreciate the prompt assistance - works great!
Hi Doug,
I think that this is what you looking for:
Please find the attached file.
Hope that helps
Haytham
Thanks you Haytham, I've been chasing my tail with sumifs and other formulas....
Hi Gents
Just a quick question, how do I avoid ending up with #N/A as an output, where a sales rep doesn't sell anything in a given month?
Please disregard my last question, I figured out the issue.
Hi Gents
Just a quick question, how do I avoid ending up with #N/A as an output, where a sales rep doesn't sell anything in a given month?
Hi Doug,
This question can be solved with lookup formula.
It can also be solved using a combination of 'match' and 'index' functions, which are my favorite for various set of problems. I've shown in the file attached.
I'm attaching the worksheet with both the formulas in it. I've used Haytam's Excel template to demonstrate.
