IF(OR Formula Help

Laura

What am I doing wrong? 

If Required Column, Rows 23-34 are "Yes" OR "No" THEN show "Yes" symbol, if NOT show "On Hold"

This is the formula I'm using:

=IF(OR(Required23 = "Yes", Required23 = “No", Required24 = "Yes", Required24 = "No", Required25 = "Yes", Required25 = "No", Required26 = "Yes", Required26 = "No", Required27 = "Yes", Required27 = "No", Required28 = "Yes", Required28 = "No", Required29 = "Yes", Required29 = "No", Required30 = "Yes", Required30 = "No", Required31 = "Yes", Required31 = "No", Required32 = "Yes", Required32 = "No", Required33 = "Yes", Required33 = "No", Required34 = "Yes", Required34 = "No"), "Yes", "Hold")

But I'm getting the "unparseable" error

[See ScreenShot]

• If Rows 23-34 "Required" (Symbol Column) are "Yes" or "No", I want Row 22, "Required" column to show "Yes"
• If Rows 23-34 "Required" (Symbol Column) are "On Hold" or Blank, I want Row 22, "Required" column to show "On Hold"

 

 

Thanks in advance! Sorry for flooding the community with formula questions lately. I'm learning, and you all give the best help!

 

 

 

Laura

=IF(OR(Required23 = "No", Required24 = "No", Required25 = "No", Required26 = "No", Required27 = "No", Required28 = "No", Required29 = "No", Required30 = "No", Required31 = "No", Required32 = "No", Required33 = "No", Required34 = "No"), "No", IF(AND(Required23 = "Yes", Required24 = "Yes", Required25 = "Yes", Required26 = "Yes", Required27 = "Yes", Required28 = "Yes", Required29 = "Yes", Required30 = "Yes", Required31 = "Yes", Required32 = "Yes", Required33 = "Yes", Required34 = "Yes"), "Yes", "Hold"))

 

Also tried this formula per Smartsheet Support. But it isn't working either. I don't receive an error but it puts "no" in row 22 if 23-34 have a no, It puts "on hold" if 23-34 have an "on hold" and it puts "yes" if 23-34 are all yes. This is not what i'm looking for

J. Craig Williams

Laura,

Try this:

=IF(OR(COUNTIF(Required23:Required34, "") > 0, COUNTIF(Required23:Required34, "Hold") > 0), "Hold", "Yes")

Craig

John Creason

Craig -

I like your suggestion.  But what if someone enters something like "tbd" in the Required column?  Based on your direction, I came up with the following which would also handle that case.

=IF(COUNT([Order Entry Notes]23:[Order Entry Notes]33) - COUNTIF(Required23:Required33, OR(@cell = "Yes", @cell = "No")) = 0, "Yes", "Hold")

John

J. Craig Williams

John,

Thank you.

"tbd" was not part of the requirements.

Since the column is a symbol column (similar to a drop down), the blank check should cover it. You solution may be inaccurate when the [Order Entry Notes] is blank. When we have covered all of the likely user errors, we must let the users find the others.

Craig

 

 

Laura

Thanks Craig! That worked. I've drove myself crazy trying to create a messy formula today!

J. Craig Williams

I don't recommend driving oneself crazy. Been there. Done that. Didn't like it. 

You are welcome.

Craig

Laura

Haha! Thanks Craig!