problem with if calculating revenue for my employees

[MAXAHMADI]

Hi all,

This is my first posting and skeptical about this. But I still need to get help. I am trying to put a formula together that allows to me track my sales associates commission. I would like to do the following:

If gross profit is below 1000, and units are at 5 or less, then its 50 per units, and if its at 6 to 10, then its 100 per, if tis at 11 to 15, then its 150., but if the gross profit is between more then 1000 and less then 2000, and units are at 5 or less, then its 200 per units, and if its at 6 to 10, then its 250 per, if tis at 11 to 15, then its 150, but if the gross profit is more then 2000, and units are at 5 or less, then its 300 per units, and if its at 6 to 10, then its 3500 per, if tis at 11 to 15, then its 400. I hope this makes sense. I have enclosed a screen shot of what I have. I have this much of the formula but it wont calculate anything over 2000 profit.

=IF((((AA10-(AH10*600))/AH10))<1000,SUMPRODUCT(((((AA10-(AH10*600))/AH10)<1000))*(AH10<=5)*AH10*50)+SUMPRODUCT(((((AA10-(AH10*600))/AH10)<1000))*(IF(AH10>=6,AH10<11)*AH10*100))+SUMPRODUCT(((((AA10-(AH10*600))/AH10)<1000))*(IF(AH10>=11,AH10<16)*AH10*150))+SUMPRODUCT(((((AA10-(AH10*600))/AH10)<1000))*(IF(AH10>=16,AH10<21)*AH10*200))+SUMPRODUCT(((((AA10-(AH10*600))/AH10)<1000))*(AH10>=21)*AH10*250),IF(((((AA10-(AH10*600))/AH10)*1000<>2000)),SUMPRODUCT(((((AA10-(AH10*600))/AH10)*1000<>2000))*(AH10<=5)*AH10*200)+SUMPRODUCT(((((AA10-(AH10*600))/AH10)*1000<>2000))*(IF(AH10>=6,AH10<11)*AH10*250))+SUMPRODUCT(((((AA10-(AH10*600))/AH10)*1000<>2000))*(IF(AH10>=11,AH10<16)*AH10*300))+SUMPRODUCT(((((AA10-(AH10*600))/AH10)*1000<>2000))*(IF(AH10>=16,AH10<21)*AH10*350))+SUMPRODUCT(((((AA10-(AH10*600))/AH10)*1000<>2000))*(AH10>=21)*AH10*400),IF(((((AA10-(AH10*600))/AH10)>2000)),SUMPRODUCT(((((AA10-(AH10*600))/AH10)>2000))*(AH10<=5)*AH10*300)+SUMPRODUCT(((((AA10-(AH10*600))/AH10)>2000))*(IF(AH10>=6,AH10<11)*AH10*350))+SUMPRODUCT(((((AA10-(AH10*600))/AH10)>2000))*(IF(AH10>=11,AH10<16)*AH10*400))+SUMPRODUCT(((((AA10-(AH10*600))/AH10)>2000))*(IF(AH10>=16,AH10<21)*AH10*450))+SUMPRODUCT(((((AA10-(AH10*600))/AH10)>2000))*(AH10>=21)*AH10*500),0)))




Can someone please help me. Thank you.

[p24leclerc]

Hi, welcome to the forum.
We would need a workbook to be able to help you.
Also, what do you call the gross profit? It seems to be a calculation. Please define it.
And when you talk about units, where is that number?
Thanks

[Sam Capricci]

Welcome to the forum. Please take a look at the forum rules, especially #1 re. Post titles. Please change your post title to one descriptive of your problem.

[MAXAHMADI]

Sorry for the lack of information. Its my first post. What I am trying to do is add up my employees Salary (AT10). I have enclosed their pay plan and my excel sheet. I need to calculate first of all
number of units (ah10) times 600(which is the companies pack) minus (front gross AB10) divided by number of units AH10
then us that in the formula. Please let me know if you need anymore information.

[FDibbins]

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[Sam Capricci]

deleted, forum mod stopped by w/same message.

[MAXAHMADI]

I am sorry, I tried to change it, what do I need to change it to. Please help, I am new to this.

[Sam Capricci]

The title should reflect your problem. That way people with a similar problem can search on it.

[MAXAHMADI]

Hi Ford, thank you for your advise. Like I said, I am not computer savvy, nor do I know a lot about excel. This is my first ever forum post. Can I get some help with what I need to change my tittle too. I really do appreciate it.

Max

[MAXAHMADI]

Hi Sambo,

I really don't know how to even describe my problem with the correct terminology. Show I write "need help with excel formula to calculate salary, with multiple criteria's".

[Sam Capricci]

You seem to have some complex if then problem to calculate revenue. If that helps.

[MAXAHMADI]

thank you. I will change my heading.

[Sam Capricci]

where is this formula? Which cell and which sheet?

Oh, I see it now.

[Sam Capricci]

that formula is giving me a headache trying to weed through it.
Have you considered the commercial services side of this forum?
BTW, it is not a good idea to merge cells and work formulas to them.

Edit: Offhand i'm sort of envisioning some sort of vlookup formula using true referencing a table in ascending order for your front end and back end parts, then a lookup formula for the rest.
I'm heading to bed and will check back on this post tomorrow sometime to see if anyone moved it further along.

[MAXAHMADI]

Thank you, I really appreciate any and all help. I am sorry I messed my table up or if I didnt know how to combine some formulas. Thank you all ffor the help.

[bebo021999]

Try in AT10 :

Please Login or Register  to view this content.

Drag down.

[MAXAHMADI]

Bebo021999, You are amazing, it worked perfectly. I don't know how to thank you. Beers on me. lol. Thank you so much.

[MAXAHMADI]

thank you, worked amazing. thank you very much.

[bebo021999]

Nice to hear that. good luck!