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

Nate H

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.

Genevieve P.

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

Paul Newcome

Are you able to provide some screenshots for reference?

Nate H

Hi, @Paul Newcome

Thanks for taking a look at this for me. The first screenshot is of the sheet the formula is going into, but it's huge so I'm only able to depict the Harvey ball section. The formula is going in a flag column further down the sheet. The second sheet is the reference sheet.

Genevieve P.

Hi @Nate H

The way I would do this is to use a COUNTIFS formula to see if a row with your matching criteria exists on your second sheet:

COUNTIFS({Program Name Column}, [Program Name]@row, {Baptist Column}, "Baptist")

If there's a row in your second sheet that has the same Program Name as this current row, and a cell that says "Baptist", then this COUNT will return at least 1. Now you can use this as the second part of your IF statement:

=IF(Baptist@row > "Empty", IF(COUNTIFS({Program Name Column}, [Program Name]@row, {Baptist Column}, "Baptist") >0, 1, 0))

Let me know if this works for you! (And Paul, let me know if I've missed something 🙈)

Cheers,

Genevieve

Paul Newcome

@Genevieve P. Thanks for the bump. This one must have gotten buried in my notifications.

The only question I have is regarding the bold portion below:

=IF(Baptist@row > "Empty", IF(COUNTIFS({Program Name Column}, [Program Name]@row, {Baptist Column}, "Baptist") >0, 1, 0))

I don't use this style of indicator very often, but are we able to use greater/less than with a "text value"? If so, where would "N/A" count? I would imagine less than "Empty", but...

Genevieve P.

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

Paul Newcome

@Genevieve P. Well that's a bummer. I was hoping there have been some update on the back-end. Haha.

Nate H

You guys are geniuses. Thank you so much @Genevieve P. and @Paul Newcome !

Paul Newcome

Teamwork!