IF THEN formula

I'm trying to incorporate a formula in one of my sheets. I'm going to use terms as if I were referring to Excel since I'm still getting familiar with the terms in Smartsheet.

For example, if cell A2 contains a date but cell B2 doesn't have a date then I want the value in cell C2 to return "50%". If cell B2 contains a date then I want cell C2 to return "100%".

I've tried using a then IF THEN formula that I'm familiar with in Excel but I'm not too sure that it applies in Smartsheet. Does anyone have any suggestions?

Tags:

Best Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Hey @AshelyBee

    This should work for you. You will need to change the A@row and B@row to the column names you are using. Smartsheet doesn't use percentage in their formulas so you will need to select percentage as a format for the cells in the column.

    =IF(ISDATE(B@row), 1, IF(ISDATE(A@row), 0.5, ""))
    

    This is another option in which 50% is either A or B being filled out and 100% being both

    =IF(AND(ISDATE(B@row), ISDATE(A@row)), 1, IF(OR(ISDATE(B@row), ISDATE(A@row)), 0.5, ""))
    
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/11/22 Answer ✓

    In Smartsheet, use ColumnName@row to designate the value in a column on this row. So we'll go with ColumnA, ColumnB, and ColumnC as the column names. ColumnC should be a Text/Number type column, and set it as a percent format by clicking the percent icon in the toolbar:

    Then try this formula in ColumnC. We're essentially adding the result values of two formulas together:

    =IF(ISDATE(ColumnA@row), 0.5) + IF(ISDATE(ColumnB@row), 0.5)

    The logic is: If the value in the ColumnA cell on this row is a Date, set the value of this formula to .5 (otherwise do nothing,) PLUS, if the value in the ColumnB cell on this row is a Date, set the value of this formula to .5 (otherwise, do nothing.)

    If both columns have date values, add .5 to .5 to equal 1, which in a percent column equals 100%. If only one has a date value, add .5 to nothing to get 50%. If neither is a date, there's 0% in ColumnC.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Hey @AshelyBee

    This should work for you. You will need to change the A@row and B@row to the column names you are using. Smartsheet doesn't use percentage in their formulas so you will need to select percentage as a format for the cells in the column.

    =IF(ISDATE(B@row), 1, IF(ISDATE(A@row), 0.5, ""))
    

    This is another option in which 50% is either A or B being filled out and 100% being both

    =IF(AND(ISDATE(B@row), ISDATE(A@row)), 1, IF(OR(ISDATE(B@row), ISDATE(A@row)), 0.5, ""))
    
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/11/22 Answer ✓

    In Smartsheet, use ColumnName@row to designate the value in a column on this row. So we'll go with ColumnA, ColumnB, and ColumnC as the column names. ColumnC should be a Text/Number type column, and set it as a percent format by clicking the percent icon in the toolbar:

    Then try this formula in ColumnC. We're essentially adding the result values of two formulas together:

    =IF(ISDATE(ColumnA@row), 0.5) + IF(ISDATE(ColumnB@row), 0.5)

    The logic is: If the value in the ColumnA cell on this row is a Date, set the value of this formula to .5 (otherwise do nothing,) PLUS, if the value in the ColumnB cell on this row is a Date, set the value of this formula to .5 (otherwise, do nothing.)

    If both columns have date values, add .5 to .5 to equal 1, which in a percent column equals 100%. If only one has a date value, add .5 to nothing to get 50%. If neither is a date, there's 0% in ColumnC.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • This works! Thank you. Is there a way for me to drag the formula to other rows or would I have to copy and paste to each row that this formula applies to?

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/11/22

    If the formula should be in every row in that column, then just right click on the formula and select "Convert to Column Formula"

    If you only need it in certain rows, click into the cell to edit the formula, select the entire formula and then hit Ctrl-C. Select the rows in that column that you want to copy into and Ctrl-V. You can Ctrl-V on any row in that column you want the formula to be in. Using the "@row" for the cell references in the formula lets you do it this way!

    Glad I could help!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Devin Lee

    one thing I love about the Smartsheet community is seeing the different ways people approach the same problem!

    It's cool that this

    =IF(ISDATE(ColumnA@row), 0.5) + IF(ISDATE(ColumnB@row), 0.5)
    

    and this

    =IF(AND(ISDATE(B@row), ISDATE(A@row)), 1, IF(OR(ISDATE(B@row), ISDATE(A@row)), 0.5, ""))
    

    do the exact same thing.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • JBolan
    JBolan ✭✭

    I have read this entire thread, and cannot seem to fix this formula: =if(Intro@row),("yes"), 6, 0) . I've moved the spacing around (no space before the numbers etc) and the parentheses / brackets and everything I can think of. What am I missing?

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 05/30/23

    @JBolan The IF function is looking for a logical statement that it can test. The syntax is IF(logical statement, value if true, value if false). Also, capitalize the functions in your formulas.

    So try this:

    =IF(Intro@row = "yes", 6, 0)

    English: IF the value in the Intro column is equal to "yes", set this cell to 6, otherwise set it to 0.

    Just FYI: Logical statements can be regular expressions like Intro@row = "yes", or the results of functions, such as ISTEXT(Intro@row). The first is testing whether the cell value matches something specific, the second is testing if the cell value is composed of text (vs a number value or boolean field.)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • JBolan
    JBolan ✭✭

    got it. so spaces between row and = plus between = and "yes". any chance Smartsheet is considering changing their spacing/punctuation rules to match excel? or at least improve tool tips to cover that part?

  • JBolan
    JBolan ✭✭

    it didn't correct the spacing for me. that was the only change made (I'd already dropped the () while waiting for a response

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    If you just removed the extraneous parentheses from =if(Intro@row),("yes"), 6, 0) to make it =if(Intro@row,"yes", 6, 0) you would still have an invalid syntax (#INCORRECT ARGUMENT SET error), but even so Smartsheet should correct the spacing to add a space between Intro@row, and "yes".

    -> then hit enter:

    -> then double click in the cell and see:


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • JBolan
    JBolan ✭✭

    thanks Jeff. knowing that's in there would solve one of the most annoying features w/smartsheet, so I'll keep an eye out for it

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!