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

Options
✭✭✭✭

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

Options

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

• ✭✭✭✭✭✭
Options

Are you able to provide some screenshots for reference?

• ✭✭✭✭
edited 09/07/22
Options

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.

Options

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

• ✭✭✭✭✭✭
Options

@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...

Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!