Nested IF AND OR

I am trying to get a formula for a check box that combines an if AND with an If OR. I have a column labeled Rental Billed that is a check box. There is another column that has multiple status options from a drop down. I want the formula to return a 1,0 if the rental billed column @row is not checked AND the Status is set to either "Complete" OR "Canceled"

Formula I cannot get to work is as follows:

=IF(AND([Rental Billed]@row <> 1, IF(OR(Status@row = "Complete", Status@row = "Canceled"))), 1, 0)

This returns an "Incorrect Argument set" error. I am sure I am missing something simple, and suspect it has to do with the AND / OR, but any help would be appreciated.

Find more posts tagged with

Accepted answers

All comments

Sean Morgan

Hello Jturton,

Thanks for reaching out within the Community!

I've done some testing and I feel I've found the solution. Your formula is essentially correct, all I did was remove the "AND", then added an "=" rather than "<>". Here is my formula. Please note that this is an example and may require adjustments to work within your sheet:

=IF([Rental Billed]@row = 1, IF(OR(Status@row = "Complete", Status@row = "Cancelled"), 1, 0))

In addition to this, here is a screenshot of my test sheet to show what I was able to achieve when using various different statuses and ticked "Rental Billed" checkboxes.:

With this formula, the "AND" is not required as the first criterion must always be matched to return the result of 1 or 0.

Let me know if you have any issues or questions, I'm more than happy to help!

Regards

Sean

Paul Chumak

@Sean Morgan

My first time here so let me know if I should be creating a new discussion, but I have a similar application:

Put in plain english:

If Cell1 is set to A OR B AND value of Cell 2 is greater than or equal to 11 then put "Yes", otherwise if Cell 1 is set to C AND the value of Cell 2 is greater than or equal to 10 put "Yes", otherwise put "No".

I've tried 25 different things and am now lost.

Sean Morgan

Hello @Paul Chumak ,

Thank you for reaching out. I don't feel this should be a seperate post as these are both regarding nested IF formulas 😊

I feel I've came up with a working solution. Please note that this is an example and may require modifications to work within your sheet.

The formula I used to create this solution was: =IF(AND(OR(ABC@row = "A", ABC@row = "B"), Numbers@row >= 11), "Yes", IF(AND(ABC@row = "C", Numbers@row >= 10), "Yes", "No"))

Please see my results below. The returned values should match the argument set:

These were the articles I used to create this formula:

Let me know if you have any questions

Regards

Sean

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.