Multiple IF AND ISBLANK Criteria in Formula

Megan Yaussi · 05/17/22

What I'm trying to accomplish:

If [Approved] is checked, change [Status] to "Complete"
If [Ad RSS Link], [Subject Line], and [Done] are not blank, change [Status] to "Proofing"
If [Ads Entered] is checked, change [Status] to "Proofing"
If none of the above are true, [Status] will be "Waiting for Information"

Here is the formula I'm using, but it keeps coming back as UNPARSEABLE. I've looked it over and retyped it a few times to no avail. Am I missing something?

=IF(NOT(ISBLANK([Approved@row])), "Complete", IF(AND(NOT(ISBLANK([Ad RSS Link]@row)), NOT(ISBLANK([Done]@row)), NOT(ISBLANK([Subject Line]@row)), "Proofing", IF(NOT(ISBLANK([Ads Entered]@row)), "Proofing", "Waiting for Information"))))

Thank you so much for the help and insight!

Paul Newcome · 05/17/22

Try this:

=IF(Approved@row = 1, "Complete", IF(OR([Ads Entered]@row = 1, AND([Ad RSS Link]@row <> "", [Subject Line]@row <> "", Done@row <> "")), "Proofing", "Waiting For Information"))

The two issues I see with your formula are:

Your first cell reference has a misplaced square bracket after "@row" instead of after the column name.
You have a closing parenthesis at the very end of the formula that needs to be moved to NOT(ISBLANK([Subject Line]@row)) so that it closes out the AND statement.

Paul Newcome · 05/17/22

Try this:

=IF(Approved@row = 1, "Complete", IF(OR([Ads Entered]@row = 1, AND([Ad RSS Link]@row <> "", [Subject Line]@row <> "", Done@row <> "")), "Proofing", "Waiting For Information"))

The two issues I see with your formula are:

Your first cell reference has a misplaced square bracket after "@row" instead of after the column name.
You have a closing parenthesis at the very end of the formula that needs to be moved to NOT(ISBLANK([Subject Line]@row)) so that it closes out the AND statement.

Megan Yaussi · 05/17/22

That worked perfectly. Thank you so much!

Alan P. · 05/17/22

At first glance, if you're using a 'Check' field for [Approved], you first condition in your if statement should look for a 1 or 0:

IF([Approved]@row = 1, "Complete".........

Paul Newcome · 05/18/22

@Alan P. You can still use the NOT(ISBLANK(.......)) for a checkbox type column. It may require a little more typing, but it still works. You can also use true for checked and false for unchecked since it is boolean.

Paul Newcome · 05/18/22

@Megan Yaussi Happy to help. 👍️

Alan P. · 05/19/22

Hi @Paul Newcome

Yeah I knew that logically, a NOT(IS BLANK will work, but I preferred to use the truncated logic of the boolean 1 and 0. Easier on the eyes and makes it a lot easier to explain if Megan wanted to share with another user onsite.

All in all, an unanswered question is answered :)

Paul Newcome · 05/19/22

@Alan P. I agree that using 1 vs 0 is much easier to read/manage.

The way I read your comment though, it seemed (to me at least) that you were suggesting that was the actual problem with the formula and not necessarily a "personal preference" type of suggestion.

Multiple IF AND ISBLANK Criteria in Formula

Best Answer

Answers

Help Article Resources

Categories