Excel - SUMIF Is Not Working - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.