# I need help on a multiple IF statement formula

Options

I'm struggling to think through the logic to allow for the required scenarios.

1. If the row is a master row - leave no flag as not at risk
2. If the row is a 1st level sub heading - leave no flag as not at risk
3. If the task isn't started by the start date = at risk!
4. If the project has started but the review date is in the past = at risk!
5. If there is no date all all in either columns - leave no flag as not at risk

MY SUM DOESN'T WORK RIGHT YET!

﻿

• ✭✭✭✭✭
Options

First, use this to identify your level: =COUNT(ANCESTORS(ID@row)) -- ID is the field, change this to whatever.

Then I would create five checkbox columns:

1. Level > 1
2. Start Date NOT >= Today
3. Target Due date... you get the point here
4. review date passed
5. the no date test (I am not sure I understand this one)

Maybe I have some of these backwards, in which case, just use NOT to get it so your desirable outcome TRUE or FALSE is accurate.

Then test the columns with your IF statements.The way I do it is I create a column for each IF statement, so that I can make sure that it works, and I use 999 in the FALSE position. For example:

Column A: IF(Test1=TRUE,"True Result",999)

and then in Column B: IF(Test2=TRUE,"True Result",999)

And then if both of those work, I place the second statement in the "999" position of the first:

IF(Test1=TRUE,"True Result",IF(Test2=TRUE,"True Result",999))

• Options

thank you very much @James Keuning with much perseverance last night I managed to arrive at a solution in the formula to avoid the extra columns.

• ✭✭✭✭✭✭
Options

Hi @Gavin Worsley

Hope you are fine, i advise you to add a helper column call it "Level" and use the following formula to define the level of the task:

=IF(COUNT(CHILDREN(task@row)) > 0, COUNT(ANCESTORS()) + 1)

then you can use this level easily to check the at-risk status of your tasks

bassam.khalil2009@gmail.com

• Options

thank you @Bassam Khalil you can't see it on the sheet in the image but I have managed to use this in the sheet to assist the formula. thank you

• ✭✭✭✭✭✭
Options

I hope you're well and safe!

I didn't get a message that you mentioned me in your post.

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!