IF Formula using multiple cell values + date

lmed80

Hello,

I have a formula for employee referral bonuses that calculates the date the bonus payout is due based on the start date if the hiring source is employee referral.

=IF([Hiring Source]@row = "Employee Referral", [Start Date]@row + 30)

This works fine for 30 and 60 days because it applies to all employees; however, for the 90 and 120 day bonus, that is specific to certain positions. I would like to add on to this formula so it also says if [Position Title]@row = and then add multiple position titles here, with the goal that the payout date will only show if it's an employee referral AND a specific job title. I can't seem to get the if/and formula right. Can someone help? Thanks in advance!

Amit Wadhwani

Hi @lmed80

Have you tried using AND function? - https://help.smartsheet.com/function/and

Thanks

Amit

https://www.linkedin.com/in/amitinddr/

lmed80

Hi Amit - yes, I did try the AND function but the syntax isn't right. I'm not sure if it's because I'm using a date column in the answer? Here's what I came up with that isn't working:

=IF(AND ([Hiring Source]@row = "Employee Referral", [Position Title]@row = “Board Certified Behavior Analyst”, “Licensed Clinical Professional Counselor”, “Licensed Clinical Social Worker”, “Licensed Professional Counselor”, “Licensed Social Worker”, “Neuropsychologist”, “Neuropsychology Technician”, “Occupational Therapist”, “Physical Therapist”, “Registered Behavior Technician”, “Speech-Language Pathologist”)), [Start Date]@row + 30

Andrée Starå

Hi @lmed80

I hope you're well and safe!

I assume you wrote your formula in Word or similar. Correct? (you have slanted "" in multiple places)

Try replacing all of the "" inside of Smartsheet, notepad, or similar.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

lmed80

Hi Andree - I forgot about that nuance...I updated the quotations marks but it's giving me the same error. Any other thoughts?

=IF(AND ([Hiring Source]@row = "Employee Referral", [Position Title]@row = "Board Certified Behavior Analyst", "Licensed Clinical Professional Counselor", "Licensed Clinical Social Worker", "Licensed Professional Counselor", "Licensed Social Worker", "Neuropsychologist", "Neuropsychology Technician", "Occupational Therapist", "Physical Therapist", "Registered Behavior Technician", "Speech-Language Pathologist")), [Start Date]@row + 30

Thanks!

lmed80

Any other assistance from anyone on this solution? Appreciate the help!!

Brent Wilson

@lmed80 First you cannot assign a logic Statement like that. Each Position Title needs to be evaluated

The Position Title would have to be in an OR statement within the AND statement

=IF(AND ([Hiring Source]@row = "Employee Referral", OR([Position Title]@row = "Board Certified Behavior Analyst",[Position Title]@row = "Licensed Clinical Professional Counselor", [Position Title]@row = "Licensed Clinical Social Worker", [Position Title]@row = "Licensed Professional Counselor", [Position Title]@row = "Licensed Social Worker", [Position Title]@row = "Neuropsychologist", [Position Title]@row = "Neuropsychology Technician",[Position Title]@row = "Occupational Therapist",[Position Title]@row = "Physical Therapist",[Position Title]@row = "Registered Behavior Technician",[Position Title]@row = "Speech-Language Pathologist"), [Start Date]@row + 30)

That being said when IF statements like this get a bit unruly I like to go to a Table/Vlookup scenario

So I would create a sheet of the job titles and if they are eligible for a 90+ bonus

So something like

Speech-Language Pathologist 1

Physical Therapist 1

Clinical Intake Co-ordinator 0

Etc..

Then you could VLOOKUP to have something like this

VLOOKUP ( [Position Title]@row,{Job Table},2,false) would return a 1 or a zero depending on the second column

Then your If would look a lot simpler

=IF(AND ([Hiring Source]@row = "Employee Referral", VLOOKUP ( [Position Title]@row,{Job Table},2,false)), [Start Date]@row + 30)

This way you can Add Positions and Remove positions without changing your If statement

You could also add a column for say 30/60/90/120 eligibility

I used something similar for determining Capital approval levels for staff so I knew who to workflow the approvals to

lmed80

Hi @Brent Wilson - thanks for the response. I am not as familiar with setting up these VLOOKUP formulas or reference sheets and can't seem to get it right with your instructions. Perhaps I am not understanding correctly? Can you elaborate a bit on how you intended for this to be set up correctly? THANK YOU!

1. I created a new sheet (Job Table) and entered all of the position titles on separate rows in the Position Title column. I then created a second column with the Bonus Eligible position titles and put either a 0 (not eligible) or a 1 (eligible).
2. I created a new column on the existing sheet (Candidate Tracker) and used the VLOOKUP formula referencing the Job Table - Bonus Eligible column, but I am getting an #incorrect argument set error. [=VLOOKUP([Position Title]@row, {Job Table - Position}, 2, false)]
3. I then reviewed your notes again and changed the first column in the Job Table to include the 0 or 1 after the position title name rather than having that as a separate column, and updated the reference to the Position Title column, but I received the same error. [=VLOOKUP([Position Title]@row, {Job Table - Bonus}, 2, false)]

Appreciate any additional guidance!

Brent Wilson

@lmed80 .. Sorry I tried to rush it..

The Job Table

The Candidate Tracker

The formula that you want in Bonus Eligible is

=IFERROR(VLOOKUP(Title@row, {Job Table - Position}, 2, false), 0)

I added the IFERROR so you do not need every Job Title in the Job Table

and the Job Table - Position

Let me know how it works

Brent Wilson

@lmed80

Another feature could be below

Rather than loading 1 or 0 you could load the days based on job title in the same table

Then your sheet would look like this without any changes to the formulas

And building upon this your Formula could then become =[Start Date]@row + [Bonus Eligible]@row

You would have to put some If the logic in it as I just realised how A SLP Student referral should not pay a bonus but would pay a bonus on zero days in my logic..

So maybe something like =IF([Bonus Eligible]@row = 0, 0, [Start Date]@row + [Bonus Eligible]@row)

Would result in the Date you are looking for

lmed80

@Brent Wilson thank you so much for taking the time to show me this! I don't think this will work because each row on the Candidate Tracker is for a new potential candidate (and could be various candidates for the same roles and at different locations), so it does not contain a full list of position titles as the Job Table does (or how you note to enter the formula on the Candidate Tracker). It's like this:

It also has 2 columns, 1 for the 90-day bonus eligibility and one for the 120-day bonus eligibility, both of which I am trying to return the date their bonus payout is due based on their hire date and position. I have a pro desk session tomorrow so will try to figure this out with the Smartsheet team, unless you have any other suggestions.

I really appreciate your help!!! Thanks again,

lmed80

@Brent Wilson actually turns out I don't have a qualifying plan for pro desk sessions :( Any additional help you or anyone else can offer would be great. Thank you!