Multiple IF/AND Statements Formula

Options

Hi All,


I have a field "Annual Hours Saved". I want to create another column with the following logic:


If Annual Hours Saved is 0 to 10, then put 1

If Annual Hours Saved is 11 to 20, then put 2

If Annual Hours Saved is 21 to 50, then put 3

If Annual Hours Saved is more than 51, then put 4


Any help would be appreciated! Thanks in advance!

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Brennan Montoni

    Try this

    =IF([Annual Hours Saved]@row<0, 1, IF([Annual Hours Saved]@row<=10,1,IF([Annual Hours Saved]@row<=20,2, IF([Annual Hours Saved]@row<=50, 3, IF([Annual Hours Saved]@row>50,4)))))

    With no screenshot, I wasn't sure if your [Annual Hours Saved] could ever be negative. I accounted for that as a 1. Change if incorrect.

    Kelly

Answers

  • Amanda Alv
    Amanda Alv ✭✭✭✭✭
    Options

    Hi there,

    Using multiple AND statements within an IF statement will get you there; the formula below will give you those answers and let you convert it to a column formula if that is more helpful.

    =IF(AND([Annual Hours Saved]@row >= 0, [Annual Hours Saved]@row <= 10), "1", IF(AND([Annual Hours Saved]@row >= 11, [Annual Hours Saved]@row <= 20), "2", IF(AND([Annual Hours Saved]@row >= 21, [Annual Hours Saved]@row <= 50), "3", IF([Annual Hours Saved]@row >= 51, "4"))))

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Brennan Montoni

    Try this

    =IF([Annual Hours Saved]@row<0, 1, IF([Annual Hours Saved]@row<=10,1,IF([Annual Hours Saved]@row<=20,2, IF([Annual Hours Saved]@row<=50, 3, IF([Annual Hours Saved]@row>50,4)))))

    With no screenshot, I wasn't sure if your [Annual Hours Saved] could ever be negative. I accounted for that as a 1. Change if incorrect.

    Kelly

  • Brennan Montoni
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!