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.

Answers

  • Sean Morgan
    Sean Morgan Employee
    edited 03/14/20

    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

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

  • 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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!