Help

Re: Multiple conditional formula with IF, AND

Solved
Jump to Solution
1039 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Jyo_D
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi Everyone, I have been trying to accomplish an error check and I have syntax issue on the below formula. May I please know what is the issue with my formula? Thank you so much in advance.

IF(AND({est_filing_date_range_start},{est_filing_date_range_end},{actual_or_estimated}!=BLANK())AND({review_type}=BLANK()),“ :stop_sign: ERROR…review_type CANNOT BE BLANK when est_filing_date_range_start, est_filing_date_range_end,actual_or_estimated fields are populated”, ",“ :white_check_mark: GOOD”)

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Right away I see a few problems…

  • You’re closing the first AND() function and then immediately adding another one with no comma separating them. The lack of a comma is itself a problem, but if all conditions are to be counted—including the single condition in the second AND() function, they must all be part of the first AND() function. In other words, there’s no need for the second one.
  • You have an extra quote and comma before your final string

As a side note, you can simplify your last two conditions (the ones where you’re currently comparing fields against BLANK(). You can use syntax similar to the first two fields and just use the field names themselves. For the final one where you want to see if it is blank, you can wrap the field reference in the NOT() function.

Addressing all of these items—and splitting it across multiple lines for easier reading—gives me this:

IF(
    AND(
        {est_filing_date_range_start},
        {est_filing_date_range_end},
        {actual_or_estimated},
        NOT({review_type})
    ),
    "🛑 ERROR...review_type CANNOT BE BLANK when est_filing_date_range_start, est_filing_date_range_end, actual_or_estimated fields are populated",
    "✅ GOOD"
)

See Solution in Thread

4 Replies 4
Justin_Barrett
18 - Pluto
18 - Pluto

Right away I see a few problems…

  • You’re closing the first AND() function and then immediately adding another one with no comma separating them. The lack of a comma is itself a problem, but if all conditions are to be counted—including the single condition in the second AND() function, they must all be part of the first AND() function. In other words, there’s no need for the second one.
  • You have an extra quote and comma before your final string

As a side note, you can simplify your last two conditions (the ones where you’re currently comparing fields against BLANK(). You can use syntax similar to the first two fields and just use the field names themselves. For the final one where you want to see if it is blank, you can wrap the field reference in the NOT() function.

Addressing all of these items—and splitting it across multiple lines for easier reading—gives me this:

IF(
    AND(
        {est_filing_date_range_start},
        {est_filing_date_range_end},
        {actual_or_estimated},
        NOT({review_type})
    ),
    "🛑 ERROR...review_type CANNOT BE BLANK when est_filing_date_range_start, est_filing_date_range_end, actual_or_estimated fields are populated",
    "✅ GOOD"
)

Thank you so much @Justin_Barrett . I have modified the formula to include the BLANK() condition and it worked.

IF(AND({est_filing_date_range_start},{est_filing_date_range_end},{actual_or_estimated},NOT({review_type})=BLANK()),“ :stop_sign: ERROR…review_type CANNOT BE BLANK when est_filing_date_range_start,est_filing_date_range_end,actual_or_estimated fields are populated”,“ :white_check_mark: GOOD”)

I omitted the BLANK() function in that context on purpose because it’s not required: NOT({field name}) will tell you if the field is empty, the same way that {field name} will tell you if it’s filled. (There is an exception to this when checking numeric fields, but that didn’t seem to be the case here, so I didn’t go deeper on that usage.) From my experience, I haven’t yet found a situation where I absolutely needed to use the BLANK() function, so I prefer to do without it wherever possible.

Thanks for clarifying that @Justin_Barrett. Your explanation makes it clear now