Having problems with IF(AND) or maybe I need IF(OR)...? Not sure.

mccoy_FSI
mccoy_FSI ✭✭✭✭✭
edited 10/05/22 in Formulas and Functions

I need to return a value based on two columns' variables.

Basically, if it's type A in the Type column I need to return a 1 or a 3 in the Ref column based on what's in the limit column. Then if it is type B in the Type column, that row should always be NA in the Ref column.

So in the above table as an example:

row one should return 1 in [Ref]

row two and three should return NA in [Ref]

row four should return 3 in [Ref]

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @mccoy_FSI

    =IF(AND(Type@row="A", Limit@row=1), 1, IF(AND(Type@row="A", Limit@row=2), 3, IF(Type@row="B", "NA")))

  • mccoy_FSI
    mccoy_FSI ✭✭✭✭✭

    Thanks, I don't get an uparseable or other error but every cell in the column is empty.

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @mccoy_FSI

    I just created a test sheet and my formula works just fine for me:

    This leads me to believe your numbers aren't really numbers on your sheet. So try this then:

    =IF(AND(Type@row = "A", VALUE(Limit@row) = 1), 1, IF(AND(Type@row = "A", VALUE(Limit@row) = 2), 3, IF(Type@row = "B", "NA")))

  • mccoy_FSI
    mccoy_FSI ✭✭✭✭✭

    I really appreciate your kind assistance, but I still can't get it to work. I have to change the words for proprietary reason, but my table is basically exactly as below, not sure if this really changes anything in the formulas you already recommended. Requirements are below the table, again your help is truly appreciated:



  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @mccoy_FSI

    It definitely wouldn't work with the 2nd formula because the Limitation column isn't numbers so VALUE won't work. You'd need to use:

    =IF(AND(Vehicle@row="Boat", Limitation@row="C"), 1, IF(AND(Vehicle@row="Boat", Limitation@row="B3"), 3, IF(Vehicle@row="Car", "NA")))

    Your problem I believe was that you probably weren't putting quotation marks around C and B3 which since they're not numbers you need to put quotes around them in the formula.

  • mccoy_FSI
    mccoy_FSI ✭✭✭✭✭
    edited 10/05/22

    This is my fault, actually all the cells weren't blank, there are some variables I left out for the column IF checks, those are returning blank. So if I just add those I should also be able to get an NA or whatever I need. So thank you, I think this is solved!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!