Seeking correct formula for "If greater than, but less than" statement.

Darcie Wilson
Darcie Wilson ✭✭✭
edited 12/09/19 in Formulas and Functions

If cell A is > 30, but < 61, then cell B is "31 - 60"




Best Answer


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Try this... You only need brackets around the column names if they have spaces. the 1 after the column name = the row you are are specifying. 

    =IF(And([Column A Name]1=>30, [Column B Name]1=<61), "31-60", "")

    If the item doesn't meet your criteria then the result will be blank.

  • Mike, Thanks for the reply.  I explained problem incorrectly.

    cell A15 has the number 31 in it.

    I need cell B15 to determine if cell A15 has a number in it that is greater than 30, but less than 61. 

    If it does, then I want B15 to say "31-60"

    Thank you for your assistance!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    While Excell and Google Sheets reference columns with Letters, formulas in Smartsheet reference the column names. Hence A15 would be [Column A Name]15 if there are spaces in the column name or ColumnName15 if there are no spaces. 

    See the following examples and replace Column A name is the name of Column A. 

    =IF(And([Column A Name]15=>30, [Column B Name]15=<61), "31-60", "")

    =IF(And(ColumnAName15=>30, ColumnBName15=<61), "31-60", "")

  • I understand about the columns... it was a poor example to use.

    But the formula isn't working...and I'm unclear why you placed Column B Name where you did?  In my mind, that should also be Column A Name. Column B Name is where I want the text to go based on Column A....but that didn't work either.  Both produced error: #INVALID OPERATION

    IF(And([Column A Name]15=>30, [Column B Name]15=<61), "31-60", "")

    Maybe the attached screenshot will help?  If not, I wont bug you anymore :)


    6-30-2017 2-10-18 PM.jpg

  • Darcie Wilson
    Darcie Wilson ✭✭✭
    Answer ✓

    I finally figured it out: =IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90")

    Thank you for your time!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Yep - sorry I got confused by the Column A and Column B thing. :) Good job. Glad I helped get you onto the right track! :)

    If you want the formula to give you the run down for each of them, nest the formulas... 

    =IF([Days Open]4>30,"Less than 30", IF(AND([Days Open]4 > 30, [Days Open]4 < 61), "30-60", IF(AND([Days Open]4 > 60, [Days Open]4 < 91), "61-90", IF(AND([Days Open]4 > 90, [Days Open]4 < 120), "91-120", IF([Days Open]4 > 120, "Greater than 120")))))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    I need to slow down. Sheesh. Thanks for catching those errors, Craig. Good thinking.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    A little testing goes a long way, Mike. I'm guilty of sacrificing quality for speed as well, but have been burned so often in my previous line of work, I get a physical pain when I know I am doing it.

    A wrong answer is worse than no answer at all.




  • Trying to have a column post a statement based on another columns %

    <1% Not Started

    100% Complete

    >1% thru <100% In Progress

    I can get the not started and Complete to work but I get an error for the in-between


    Here is the formula I have used

    =IF([% Complete]90 =0, "Not Started", IF([% Complete]90 = 1, "Complete", IF(AND([% Complete]90, >.01, [% Complete]90, <.99, "In Progress"))))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!