Formula Help for IF/And/Or

Ed S · 08/09/22

I'm trying to write a formula which sets the At Risk Flag.

If the Status="At Risk" the flag should be set

Or

If the End Date is Greater than Today AND the Status is not equal to "Complete" the Flag should be set.

Otherwise, the flag should not be set.

Here's what I came up with. It works correctly when the date is after today and the status is not equal to complete, but the flag does not set if the status is changed to At Risk.

=IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Complete")), OR(Status@row = "At Risk", 1, 0))

Any help is appreciated!

Ed

Jeff Reisman · 08/09/22

@Ed S

This is a common error when combining ORs and ANDs. You need your AND statement to be one of the options inside the OR statement:

=IF(OR(Status@row = "At Risk", AND([End Date]@row < TODAY(), NOT(Status@row = "Complete")), 1, 0)

In English: If the status is "At Risk," OR if both the end date is before today AND the status is not "Complete," set the flag, otherwise do not set the flag.

Ed S · 08/09/22

Hi Jeff,

Thanks for the reply. I copied the formula and am getting the message #incorrect argument set. Any idea's what might cause that?

Thanks,

Ed

Jeff Reisman · 08/09/22

*Edited because I missed an end parentheses.

It always helps to share a screenshot of your formula as it appears while editing it in Smartsheet, with the color-coding.

But let's try this first, and if this doesn't work, share the screenshot:

=IF(OR(Status@row = "At Risk", AND([End Date]@row < TODAY(), Status@row <> "Complete")), 1, 0)

Ed S · 08/09/22

That worked PERFECTLY! Thank you very much.

Ed

Formulas and Functions

Formulas and Functions

Formula Help for IF/And/Or

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of values in a multi-select dropdown list

How would I write a formula to locate duplicates in 1 column and the difference in another column.

#NO MATCH result but not for identical formulas