IF AND Function

Dear Smartsheet Wizard,

I'm trying to create a function but I'm running into an error.

The IF - And function I'm creating basically needs to do the following:

If salesperson 1 is = "Yan" then deduct 100 from Profit and his commission should be profit*0.5. Otherwise, deduct 100 from Profit and commission should be profit*0.4.

The second IF is:

IF Date>9/3/19 and Dealer = "B C" than if salesperson 1 is "Yan" then deduct 175 from Profit and his commission should be profit*0.5. Otherwise, deduct 175 from Profit and commission should be profit*0.4.

What I did looks something like this.

=IF(AND(Date1513>9/3/19,Dealership1="B C",[Sales Person 1]1513="Yan",)[Net Profit]1513 - 175) * 0.5),([Net Profit]1513 - 175) * 0.4)IF([Sales Person 1]1513= "Yan", ([Net Profit]1513 - 100) * 0.5), ([Net Profit]1513 - 100) * 0.4))

Please help.

Thank you.

Find more posts tagged with

Sales

Services

Account and User Management

Comments

Connor Hartford

Yeshaya,

What is the significance of the date (9/3/19)? Is there a reason that is in your formula or did you mean to reference a cell with a date inside of it?

If you put this formula in other cells then it will always reference 9/3/19, is that what you want?

Connor

L_123

I spot 2 errors off the top just looking at the formula. both are bolded below

=IF(AND(Date1513> 1. 9/3/19 ,Dealership1="B C",[Sales Person 1]1513="Yan",)[Net Profit]1513 - 175) * 0.5),([Net Profit]1513 - 175) * 0.4 2. )I F([Sales Person 1]1513= "Yan", ([Net Profit]1513 - 100) * 0.5), ([Net Profit]1513 - 100) * 0.4))

1. Date needs to be told it is a date. to do this you use the date formula

>date(2019, 9,3)

2. you have already fulfilled all potential conditions of your if statement. You cannot simply add on another if. a simpler example of what you have done is below

=if( a = 1, true, false)if(a = 2,1,0)

You see how the second if statement has not clear relation to the first?

Each if statement gets 2 returns, a true and a false. When you create a branch you add more of what I like to call filter levels for the data, until you get to what you want.

Your formula and description don't really give enough information for us to give you a full answer, as we don't know what particular condition causes the change between the two if statements. Is it because the date is different? or the dealership is different? the only thing I see really correlating the two formulas is "Yan".

Yeshaya

Hi L@123

Thank you for your time and thoughts.

There are two conditions that cause a change; namely,

1. If the salesperson is Yan.

2. If the dealer is B C.

If the salesperson is Yan then he gets 50% of Net Profit. Otherwise, the salesperson gets 40%.

If the date is after 9/3/19 and the dealer is B C, deduct $175 from the profit. Otherwise, deduct $100.

I very much appreciate your advice.

Yeshaya

Now I'm trying to do just the below formula for starters. For some reason, it still shows an error. Please help.

IF(AND(Dealership1534 = "B C", >DATE(2019, 9, 2), [Sales Person 1]1534 = "Yan"), ([Net Profit]1534 - 175) * 0.5, ([Net Profit]1534 - 175) * 0.4)

Thank you!

L_123

you just post > Date() What is greater than the specified date? an example would be

Date1534 > DATE(2019,9,2)

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.