Need help combining IF, AND/OR formulas


Hey yall! I am trying to write a formula that will read the 'Check In' column and "Last Wk Check In' column and check the '2 Wk - No Check In' flag if the following cases occur:

  • If both columns read 'No Check In' (that flag is currently working as can be seen in the screenshot below)
  • If the columns both read "No Data', I would like the '2 Wks - No Data' AND "2 Wks - No Check In' to be flagged
  • If there is a combination of 'No Data' and 'No Check In' (ex student - Antonio James), I would like '2 Wks - No Check In' to be flagged but NOT '2 Wks - No Data' to be flagged



  • Brent Wilson
    Brent Wilson ✭✭✭✭✭

    @Kishan Naik RA

    2 Weeks - No Data is simple as you are only checking for one case based on your description

    =IF( AND([CHECK IN] = "No Data",[Last Wk Check In] = "No Data"), 1, 0)

    2 Weeks - No Check in

    =IF( AND([CHECK IN] = "No Check In",[Last Wk Check In] = "No Check In"), 1, IF( AND([CHECK IN] = "No Data",[Last Wk Check In] = "No Data"), 1, IF(OR(AND([CHECK IN] = "No Data",[Last Wk Check In] = "No Check In"),AND([CHECK IN] = "No Check In",[Last Wk Check In] = "No Data"), 1, 0)))

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

  • Kishan Naik RA

    Not sure what I am doing wrong but I keep getting an 'Incorrect Argument Set' error when I put in the formula. Below are some screenshots to show the error, thanks for your help!

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @Kishan Naik RA

    Your 2nd IF(AND( doesn't have an end parenthesis in it:

    I circled the area for you. I'm not sure but you also may need to have more than 1 end parenthesis in this spot. Actually I'm pretty sure you do:

    It's easy to tell if you pay attention to the tooltip info while you click around in different positions of your formula.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!