Trying to combine 2 AND functions with an OR

Jeff Fisher

Hi There!

I am new to SmartSheets and am working on a way to automate a process using the OR with AND's and running into trouble.

Basically, I am trying to check a box in a column if any of the following is true.

If the Model type is 1 AND the version is less than 9

OR

If the model is 2 AND the version is less that 18

Below is what I have been trying. The result here is if the model is 1 and less than 9 the box is checked but if the model is 2 the box never gets checked no matter what value is entered. My formula seems to be ignoring the 2nd set of parameters.

=IF(AND([MODEL]@row = 1), AND([VERSION]@row < 9), OR(AND([MODEL]@row = 2, AND([VERSION]@row < 18), true, false)))

Thanks!

Jeff

Paul Newcome

Take a gander at this...

The OR function requires logical statements.

OR(logical statement 1, logical statement 2)

The AND function works the same way.

AND(logical statement 1, logical statement 2)

Both the OR and the AND functions can be used as a logical statement, so first build your ANDs.

AND(MODEL@row = 1, VERSION@row < 9)

AND(MODEL@row = 2, VERSION@row < 18)

Then we use the AND statements as our logical statements within the OR function.

OR(AND(MODEL@row = 1, VERSION@row < 9), AND(MODEL@row = 2, VERSION@row < 18))

Because the OR functions as a logical statement in and of itself, this would be the logical statement within your IF.

=IF(OR(AND(MODEL@row = 1, VERSION@row < 9), AND(MODEL@row = 2, VERSION@row < 18)), true, false)

And there you have it.

Paul Newcome

@meganellis07 Give something like this a try:

=COUNTIFS([Task Type]:[Task Type], @cell = "Project", Status:Status, OR(@cell = "Completed", @cell = "In Progress"))

Paul Newcome

Take a gander at this...

The OR function requires logical statements.

OR(logical statement 1, logical statement 2)

The AND function works the same way.

AND(logical statement 1, logical statement 2)

Both the OR and the AND functions can be used as a logical statement, so first build your ANDs.

AND(MODEL@row = 1, VERSION@row < 9)

AND(MODEL@row = 2, VERSION@row < 18)

Then we use the AND statements as our logical statements within the OR function.

OR(AND(MODEL@row = 1, VERSION@row < 9), AND(MODEL@row = 2, VERSION@row < 18))

Because the OR functions as a logical statement in and of itself, this would be the logical statement within your IF.

=IF(OR(AND(MODEL@row = 1, VERSION@row < 9), AND(MODEL@row = 2, VERSION@row < 18)), true, false)

And there you have it.

Jeff Fisher

Brilliant, that works perfectly and explains where my thinking was off. I appreciate the concise explanation!

thank you very much Paul!

Jeff

Paul Newcome

Happy to help! 👍️

Margaret Griffin

Is there any way to achieve the same thing when working with values in a drop-down field and check boxes? I am trying to check a box in a column if any of the following is true:

If [Gift Type] equals "Planned Gift" AND [Planned Giving Approval] is checked

OR

If [Gift Type] is one of "Outright", "Pledge", "GIK", "Securities", "IRA Rollover" AND [DoG Approval] is unchecked

I'm able to get the IF formulas to work for the checkbox true/false. But I haven't found a viable formula for selecting multiple possible values in a cell. And I have no idea how to configure the syntax to combine the two in and AND/OR formula. Thanks for any help you can offer!

Paul Newcome

@mgriffin If you break it down piece by piece, you can actually write it out very similar to how you have it written out now. Then you can use that to build your formula.

[Gift Type]@row = "Planned Gift"

AND

[Planned Giving Approval]@row = 1

AND([Gift Type]@row = "Planned Gift", [Planned Giving Approval]@row = 1)

[Gift Type]@row = "Outright"

OR

[Gift Type]@row = "Pledge"

OR

[Gift Type]@row = "GIK"

OR

[Gift Type]@row = "Securities"

OR

[Gift Type]@row = "IRA Rollover"

OR([Gift Type]@row = "Outright", [Gift Type]@row = "Pledge", [Gift Type]@row = "GIK", [Gift Type]@row = "Securities", [Gift Type]@row = "IRA Rollover")

The above OR

AND

[DoG Approval]@row = 0

AND(OR(................................), [DoG Approval]@row = 0)

AND(OR([Gift Type]@row = "Outright", [Gift Type]@row = "Pledge", [Gift Type]@row = "GIK", [Gift Type]@row = "Securities", [Gift Type]@row = "IRA Rollover"), [DoG Approval]@row = 0)

The first AND

OR

The second AND

OR(AND(...............), AND(................))

OR(AND([Gift Type]@row = "Planned Gift", [Planned Giving Approval]@row = 1), AND(OR([Gift Type]@row = "Outright", [Gift Type]@row = "Pledge", [Gift Type]@row = "GIK", [Gift Type]@row = "Securities", [Gift Type]@row = "IRA Rollover"), [DoG Approval]@row = 0))

Then we drop this into your IF...

=IF(OR(AND(...............), AND(................)), 1)

=IF(OR(AND([Gift Type]@row = "Planned Gift", [Planned Giving Approval]@row = 1), AND(OR([Gift Type]@row = "Outright", [Gift Type]@row = "Pledge", [Gift Type]@row = "GIK", [Gift Type]@row = "Securities", [Gift Type]@row = "IRA Rollover"), [DoG Approval]@row = 0)), 1)

TroyT

I have a similar issue as the above, but it includes an iferror. Here is the formula i am using now, which works fine:

=IFERROR(IF(AND(Subscription@row = 1, Status@row = "Subscription - Pending", [Subscription Year In Sequence]@row = 1), [Contract Value]@row, 0), 0)

I need to add a few OR functions to the same formula, listed here:

OR([Subscription Year]@row = "1 of 3", [Subscription Year]@row = "1 of 2", [Subscription Year]@row = "1 of 4", [Subscription Year]@row

I tried the below with no luck, any thoughts?

=IFERROR(IF(OR(AND(Subscription@row = 1), AND(Status@row = "Subscription - Pending")), [Subscription Year]@row = "1 of 3", [Subscription Year]@row = "1 of 2", [Subscription Year]@row = "1 of 4", [Subscription Year]@row = "1 of 5", [Contract Value]@row, 0), 0)

Paul Newcome

@TroyT Give this one a go...

=IFERROR(IF(AND(Subscription@row = 1, Status@row = "Subscription - Pending", [Subscription Year In Sequence]@row = 1, OR([Subscription Year]@row = "1 of 3", [Subscription Year]@row = "1 of 2", [Subscription Year]@row = "1 of 4")), [Contract Value]@row, 0), 0)

TroyT

@Paul Newcome you make it look easy, thanks for the assist!

Paul Newcome

@TroyT Happy to help. 👍️

You were pretty close to the right idea. Basically the OR function is an argument within the AND function.

=AND(this = that, OR(................))

Michelle Barnett

I have a similar question. I'm not sure what should come first, AND or OR. I'm trying to evaluate two conditions to find the correct rate. If the role is App Developer and the person is an onshore employee or an onshore contractor the rate is $185.00. If the role is App Developer and the person is an offshore employee or an offshore contractor, the rate is $90.00. I read through the comments above, and I've broken out the and statements.

=IF(AND(Resource2 = "App Developer", [Resource Location]2 = "Onshore Employee"), "$185.00", "$90.00")

=IF(AND(Resource2 = "App Developer", [Resource Location]2 = "Onshore Contractor"), "$185.00", "$90.00")

I know these work.

when I add the or it doesn't work.

=IF(OR(AND(Resource1 = "App Developer", [Resource Location]1 = "Onshore Employee"), OR(Resource1 = "App Developer", [Resource Location]1 = "Onshore Contractor"), "$185.00", "$90.00")))

=IF(OR(AND(Resource1 = "App Developer", [Resource Location]1 = "Onshore Employee"), AND(Resource1 = "App Developer", [Resource Location]1 = "Onshore Contractor"), "$185.00", "$90.00")))

What am I doing wrong with this formula?

Thanks.

Michelle

Paul Newcome

@Michelle Barnett It is only a matter of misplaced parenthesis. Forget about the actual statements inside of each function and you are left with

IF(this is true, then this, otherwise this)

AND(......)

AND(......)

OR(......)

You want the two AND functions inside of the OR function.

OR(AND(......), AND(......))

And that is going to be the "if this is true" portion of the IF

=IF(OR(AND(......), AND(......)), then this, otherwise this)

So if you take a look at your very last formula, all we really need to do is close off the OR to include both ANDs and remove two of the closing parenthesis from the end of the formula.

=IF(OR(AND(Resource1 = "App Developer", [Resource Location]1 = "Onshore Employee"), AND(Resource1 = "App Developer", [Resource Location]1 = "Onshore Contractor")), "$185.00", "$90.00")

.

.

.

Another quick observation... If you are putting this into a text/number column and want to be able to use this output in the future for something such as a SUM or AVG function, you are actually outputting text strings which won't operate like a number. If you just output a plain number and format the column to be currency, you will have the same visual, but you will have numbers stored on the back end that can be used as actual numbers.

=IF(OR(AND(Resource1 = "App Developer", [Resource Location]1 = "Onshore Employee"), AND(Resource1 = "App Developer", [Resource Location]1 = "Onshore Contractor")), 185, 90)

Michelle Barnett

Wow, that's great, Paul. Thanks for your assistance. Yes, I am going to use it as a number so I'll incorporate your observation above. Thank you again for your assistance resolving this.

Paul Newcome

Happy to help. 👍️

BJTidi

Trying to give a value of 1 if two statements are correct

if "owner" = name and "target completion date" is less than today

or

if "owner" = name and "target completion date" is blank

Getting the dreaded "unparseable" with the following formula

=if(or(and(owner@row="NAME", "Target Completion Date"@row<TODAY),1,0)OR(owner@row="Name", "Target Completion Date"@row=(ISBLANK)),1,0))

I'm guessing I have too many parenthsis or in wrong place?

Paul Newcome

@BJTidi Try this instead...

=IF(AND(Owner@row = "Name", OR([Target Completion Date]@row < TODAY(), [Target Completion Date]@row = "")), 1)