Issue with formula (Contains, If and or)

DHR Temp Assignment Team
DHR Temp Assignment Team ✭✭✭✭✭
in Formulas and Functions

Hi, I cannot seem to get this formula to work —

I want to flag any requests for rapid ads (ad request type contains "rapid" for two types of classifications (class type contains "mgr" or "sup"). This is what the formula looks like but it is not working properly so I know something is wrong… any help would be most appreciated? 

=IF(CONTAINS([AD REQUEST TYPE]@row, "RAPID"), AND(OR(CONTAINS([CLASS TITLE]@row, "sup"),
CONTAINS([CLASS TITLE]@row, "mgr"))),"do not rapid post unless specialist capacity"," "

· Share on FacebookShare on Twitter

Best Answers

  • Kerry St. Thomas
    Kerry St. Thomas ✭✭✭✭✭✭
    Answer ✓

    There's an IF missing, the way you've written the formula. It appears to be where the AND currently is -

    … "RAPID", IF(OR(CONTAINS(…

    The way the formula is currently written, it's only evaluating "RAPID" in the Ad Request Type as TRUE or FALSE. More about the IF formula structure:
    https://help.smartsheet.com/function/if

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

    · Share on FacebookShare on Twitter
  • Davin Vo
    Davin Vo ✭✭✭
    Answer ✓

    Hello @DHR Temp Assignment Team

    Just rearranging the formula a bit

    You can try:

    =IF(AND(CONTAINS("RAPID",[AD REQUEST TYPE]@row),OR(CONTAINS("sup",[CLASS TITLE]@row),CONTAINS("mgr",[CLASS TITLE]@row))),"do not rapid post unless specialist capacity","")

    Output:

    image.png

    The above checks to see if AD REQUEST TYPE contains "Rapid" AND the Class Title contains either "Sup" or "Mgr". You can change the True Statement "Do not Rapid Post Unless Specialist Capacity" as needed, along with the false statement right after. As a reminder CONTAINS Syntax is (Search_For, Search_Within).

    Hope that helps!

    Davin Vo - Sevan Technology

    Smartsheet Platinum Partner

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!