If AND/OR statement (multiple)

[Ali_Shannon]

hi all! Trying to create a logical formula but having some trouble:

If Title is empty AND Copy Request is empty = “no copy needed”
If Title is not empty AND Copy Request is not empty = “copy provided”
If Title is not empty BUT Copy Request is empty = “copy provided”
If Title is empty BUT Copy Request is not empty - “copy requested”

Hoping this is possible but getting really stuck creating a formula past a simple AND formula :upside_down_face:
Thank you in advance!

[JonathanBowen]

Hi @Ali_Shannon - what you need is a combination of IF, AND and NOT. Side note - generally, in a logical sense, “BUT” means “AND” (and we don’t have a BUT function in any case), but please clarify if you meant something other than “AND” where you have “BUT”.

Strictly, the formula is:

IF(
  AND(NOT({Title}), NOT({Copy Request})), 
  'No copy needed',
  IF(
    AND({Title}, {Copy Request}), 
    'Copy provided',
    IF(
      AND({Title}, NOT({Copy Request})), 
      'Copy provided',
      IF(
        AND(NOT({Title}), {Copy Request}), 
        'Copy requested'
      )  
    )
  )    
)

So we use NOT({Some field}) to check for an empty field and we can just use the field itself, e.g. {Title}, to check if the Title field has a non-empty value. We use AND to combine the empty/not-empty conditions.

However, we can simplify the formula further as conditions 2 and 3 give rise to the same output. Across these two conditions we’re really saying “if the title is not empty, the result is copy provided, irrespective of the copy request value”, so the formula could be:

IF(
  AND(NOT({Title}), NOT({Copy Request})), 
  'No copy needed',
  IF(
    {Title}, 
    'Copy provided',
    IF(
      AND(NOT({Title}), {Copy Request}), 
      'Copy requested'
    )  
  )    
)

and you will get the same result: