Formula Using IFS, Contains, and AND

Rachel Heard

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!

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

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

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

Rachel Heard

That worked, thanks!!

Paul Newcome

Happy to help! 👍️