Looking for formula to trigger flag based on multiple conditions across two sheets


Hello, community!

I need help with what I assume will be a rather complicated IF formula. I have a flag that I need to trigger based on a column (Baptist) that has a Harvey ball at a quarter, or half, or three-quarter, or full, plus cross-reference the [Program Name]@row with another sheet to find the corresponding row see if the client name is what is hard-coded, such as {Sheet 2 Client Reference Range} = "Baptist".

This is what I came up with but I get #invalid.

=IF(AND(Baptist@row > "Empty"), IF(AND([Program Name]@row = {Program Testing Master Sheet Range 2}, {Program Testing Master Sheet Range 1} = "Baptist"), 1, 0))

I appreciate your help.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Oh my goodness, I completely missed that. Good catch!

    =IF(OR(Baptist@row = "Quarter", Baptist@row = "Half", Baptist@row = "Three Quarter", Baptist@row = "Full"), IF(COUNTIFS({Program Name Column}, [Program Name]@row, {Baptist Column}, "Baptist") >0, 1, 0))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!