Question description:
This user has given permission to use the problem statement for this
blog.
I am working on a cash flow projection. Part of the projection includes sales commissions. Our sales guys earn a monthly draw and then commission on sales after a certain amount. For example they may earn a monthly salary of 12,500 and earn additional commission after their commission equals $150,000. What I need excel to do is sum a column if the values in the preceeding columns are greater than $150,000. I've tried using the sumif and the if function in excel and it's not working correctly either way. Any suggestions?
Solved by G. W. in 19 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
08/08/2018 - 03:36
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User
08/08/2018 - 03:37
Thanks!
Excelchat Expert
08/08/2018 - 03:38
I've read your concern, and it seems you wish to perform a SUM function when a certain condition is met. Will you be able to share the file you're working on? Or if not, it would be great if you can provide a sample data we can work on in the document preview to the right.
User
08/08/2018 - 03:38
The file contains sensitive information. I'll copy a sample to the document on the right.
Excelchat Expert
08/08/2018 - 03:38
Sure thing, please do!
User
08/08/2018 - 03:40
So basically we pay this guy $29,167 a month and he earns a certain percentage of commission on each deal
User
08/08/2018 - 03:41
The commission is the $68,750 and other amounts at the bottom. He only gets commission once the total is over $350,000
User
08/08/2018 - 03:41
So the formula needs to calculate 29,167+any commission due for each month in row 2
Excelchat Expert
08/08/2018 - 03:42
In cell K2, the value increased to 291,666. Was this supposed to be as such, or should it as well be just 29,166
User
08/08/2018 - 03:43
Sorry that was my "key" that is the amount I know that row should be if the formula is working correctly.
User
08/08/2018 - 03:43
same with L2
Excelchat Expert
08/08/2018 - 03:43
Oh I see. Okay, let me test the values first.
Excelchat Expert
08/08/2018 - 03:44
How was the K2 value computed? I tried adding 29,166+the sum of the previous commissions, but it ended up way bigger.
Excelchat Expert
08/08/2018 - 03:47
It seems the added commission in cell K2 was 262,500. May I ask how did we arrive with this value considering the commissions previously and on that month as well. Thanks!
User
08/08/2018 - 03:48
29,167+275,000-(350,000-337,500)
User
08/08/2018 - 03:48
where 2750,000 is november's total commission and 337,500 is the previous commission earned.
User
08/08/2018 - 03:49
he only gets commission after the total commission reaches 350,000
Excelchat Expert
08/08/2018 - 03:49
Oh, okay I see. Thanks for that clarification!
User
08/08/2018 - 03:49
Sure, it's a little confusing.
Excelchat Expert
08/08/2018 - 03:57
Okay, already done! If you look at B2 to L2, I've added a single formula that computes for the total salary based on how you've described the conditions. :)
Excelchat Expert
08/08/2018 - 03:57
By the way, I linked the standard salary to cell O2 so that you could change that anytime, and thus the formula will as well autoupdate.
User
08/08/2018 - 03:58
Thanks!
Excelchat Expert
08/08/2018 - 03:59
Btw, you can download a copy of the document preview by going to File > Download as > Microsoft Excel (.xlsx). The formulas will be downloaded as well. :)
Excelchat Expert
08/08/2018 - 03:59
Do you have any more clarifications with the provided solution? :)
User
08/08/2018 - 03:59
I don't think so. That cleared it up! Thank you so mcuh!!
Excelchat Expert
08/08/2018 - 04:00
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
