IF(AND Invalid Operation Error


I need help fixing this formula - keep receiving an Invalid Operation error.

I'm trying to return "No" if an email from the row in the Smartsheet file matches an e-mail from the cross-sheet reference's file in the E-mail column and that cross-sheet reference's row also contains "Mgmt" in the User Role column. (So two conditions must be met from the cross-reference sheet in order to return a "No")

=IF(AND([User Email]@row = {E-mail}, {User Role} = "Mgmt"), "No", "Yes")




  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need to use a COUNTIFS to count how many rows match the two criteria and then nest it in an IF to say "IF the COUNTIFS is greater than or equal to 1 then "No", otherwise "Yes".

    So basically you want to take out the AND completely and replace it with the COUNTIFS is greater than or equal to 1 portion.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!