Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Help combining IF, AND, and OR

Topic Labels: Formulas
259 2
cancel
Showing results for 
Search instead for 
Did you mean: 

I would love if someone could help me work through how to set up a formula for the following situation. We have a volunteer application form for which we ask for applicants to provide 3 references. When determining whether to approve their application, we require 2 of the 3 references to be positive. If one of the three references doesn’t respond or identifies concerns, they are still approved if we receive the other 2 and they’re both positive. Basically, I need a formula that says if any 2 of these 3 fields has the value of “No concerns”, the approved column value becomes “Yes”.

I’ve figured out how to use IF-AND parameters to set up a formula that says if all 3 are labeled no concerns, it’s marked yes, but can’t figure out how to incorporate an OR in there to allow for approval if any 2 of the 3 fields are labeled no concerns regardless of which 2 they are.

I’m sure in plain English, my formula should probably be something like:
If (reference 1 & reference 2) or (reference 2 & reference 3) or (reference 1 & reference 3) are labeled “no concerns,” approved column value is “yes.” Can someone help me make this happen with actual formula formatting?

Thanks so much!

2 Replies 2

You can assign a value of 0 or 1 to each reference and see if the the sum is at least 2. This logic is a bit different from what you proposed, but it will be easier to adapt if the number of references ever increases.

IF(
  IF({reference1} = "No concerns", 1, 0) + 
  IF({reference2} = "No concerns", 1, 0) + 
  IF({reference3} = "No concerns", 1, 0) >= 2,
  "Yes"
)

Thanks so much! That definitely seems like it’ll do what I need. I’ll give it a try!