Formula Using IFS, Contains, and AND

I am trying to troubleshoot the following formula:

=IFS((CONTAINS("Shadow", {GI Clinic 3 Session})), AND([Provider Last Name]3), "Scheduled")

Logic:

If the cross-referenced sheet (GI Clinic 3 Session) contains the word "Shadow" and the Provider Last Name is the name in cell 3 (current sheet), then say "Scheduled."

Everything seems to work up until "Scheduled." Any help is appreciated!

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

Ok. You would want to use a COUNTIFS to count how many times the provider name and session type of "Shadow" are in the same row then use an IF statemet to say that if the count is higher than zero, output "Scheduled".

=IF(COUNTIFS({Source Sheet Provider Name Column}, [Provider Last Name]@row, {Source Sheet Session Type Column}, "Shadow") > 0, "Scheduled")

All comments

Paul Newcome

Are you able to provide a screenshot of the source and target sheets with sensitive data removed, blocked, or replaced with "dummy data" as needed?

Rachel Heard

Hi Paul, thanks for your quick response. Please see the screenshots below:

Main Sheet:

Reference Sheet:

Paul Newcome

=IF(COUNTIFS({Source Sheet Provider Name Column}, [Provider Last Name]@row, {Source Sheet Session Type Column}, "Shadow") > 0, "Scheduled")

Rachel Heard

That worked, thanks!!

Paul Newcome

Happy to help! 👍️

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.