IF(AND Invalid Operation Error

Hi!

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")

Thanks!

Find more posts tagged with

Formulas

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.