Help combining IF, AND, and OR

[Megan_Woolston]

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!

[kuovonne]

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"
)

[Megan_Woolston]

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