Create a formula that breaks down multiple criteria?

Chantal Wiesner

I am trying to create a formula that fills in the checkbox when certain criteria are met.

For context, the formula needs to read two different cells to figure out if the checkbox needs to be filled out.

IF the 'QT Formula - DO NOT DELETE' row is less than 4, AND the 'Delivery-DO NOT DELETE' row is one of "Virtually-Hosted" or "Remote-Hosted", then I want the box to be checked. OR, if 'QT Formula - DO NOT DELETE' row is less than 30 AND the 'Delivery-DO NOT DELETE' row is one of "In-Person Hosted".

This is what I have for the first part of the formula so far... =IF(AND([QT Formula - DO NOT DELETE]@row < "4", [Delivery- DO NOT DELETE]@row = "Remote-Hosted"), 1, 0)

Can anyone help? I know this is a lot for one formula. Here is some of the dataset for reference.

QT is currently the box we are have conditionally formatted and are manually checking when conditionally formatting is met. Thank you!

Jon Mark H

Hey!

This was a fun one to tackle!

Looks like this does it:

=IF(OR(AND([QT Formula - DO NOT DELETE]@row < 4, OR([Delivery - DO NOT DELETE]@row = "Virtually-Hosted", [Delivery - DO NOT DELETE]@row = "Remote-Hosted")), AND([QT Formula - DO NOT DELETE]@row < 30, [Delivery - DO NOT DELETE]@row = "In-Person Hosted")), true, false)

You'll have to double check that I my input has the exact spelling & spaces that you had - but it should work.

Also - as I noticed you have DO NOT DELETE in one of your formula column - just wanted to see if you know about making Column Formulas (less easy to delete and best practice if you need a formula in an entire column.

Let me know if it works or if you need any other help!

-Jon Mark

Chantal Wiesner

Hi Jon Mark H,

Thank you so much for your help - that formula worked!

And the DO NOT DELETE is for the column itself, our manager really likes to keep things clean so its happened in the past that things have been deleted accidentally, so now we label them with DO NOT DELETE to avoid that (haha).

Could I ask for help with one more layer to this formula, I want to add in that if the Delivery is "Self-Hosted" and the QT formula is less than 3, then the box is checked.

Thanks again for all of your help with writing this formula, its really helped save me a lot of time/work!

Jon Mark H

@Chantal Wiesner

Haha - that's funny about your column naming!

Yes - no problem, looks like this should get you that extra layer:

=IF(OR(AND([QT Formula - DO NOT DELETE]@row < 4, OR([Delivery - DO NOT DELETE]@row = "Virtually-Hosted", [Delivery - DO NOT DELETE]@row = "Remote-Hosted")), AND([QT Formula - DO NOT DELETE]@row < 30, [Delivery - DO NOT DELETE]@row = "In-Person Hosted"), AND([Delivery - DO NOT DELETE]@row = "Self-Hosted", [QT Formula - DO NOT DELETE]@row < 3)), true, false)

Let me know if you need any other help!

-Jon Mark