Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

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

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

    image.png

    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]

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • ✭✭✭✭✭✭

      @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")))

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      @mccoy_FSI

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

      image.png

      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")))

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      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:


      image.png


      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

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

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭
      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!

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions