Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Help with IF/AND formula

Joshua Rose
edited 12/09/19 in Archived 2017 Posts

Hi--I've been reviewing sample Smartsheet formulas from discussions past, but can't quite get my (relatively simple) formula to work.


I'm trying to return a checkbox for a given row (let's say 1) if "Rehab?" column is "Yes" AND "Xtra Cost (cog, rehab, tx, etc.) is blank. 


I've tried several different formulas, but most recently:


=IF(AND([Rehab?]1 = Yes, [Xtra Cost (cog, rehab, tx, etc.)]1 = 0), 1, 0)


I'm not really sure how to indicate blank (I tried = 0) for starters...


Thanks for any help.


P.S. For future reference, would be good to know how to indicate not blank, i.e. if anything is entered in a given field.




  • Joshua Rose
    edited 02/15/17

    José Joaquín


    Mil gracias. The devil's always in the details. Saludos!



  • Hi Joshua,


    the formula is ok,  just remenber to surround text strings with quotation marks.


    =IF(AND([Rehab?]1 = "Yes", [Xtra Cost (cog, rehab, tx, etc.)]1 = 0), 1, 0)


    I like use "" to indicate blank, I feel it more clear, but 0 work too.

    And there's a formula to check if a cell is blank ISBLANK(@cell)


    =IF(AND(Rehab?1 = "Yes", ISBLANK([Xtra Cost (cog, rehab, tx, etc.)]1)), 1, 0)

    (This formula doesn't work if the Xtra Cost is 0, just when Xtra Cost is blank)


    Regards, José Joaquín.

  • Joshua Rose

    =IF(AND(Eval Payment Status]1 = "Appointment Pending (pay plan)", [Initial Pay Plan Amount Due]1 = 0), 1, 0)


    I'm losing my mind. Why would this not be working. Exact same logic...! Surprised

  • Hi Joshua,


    You miss an open square bracket before Eval Payment Status.

    =IF(AND([Eval Payment Status]1 = "Appointment Pending (pay plan)", [Initial Pay Plan Amount Due]1 = 0), 1, 0)


    Regards, José Joaquín.

  • Joshua


    I see you want to use the formula in a checkbox. First make sure that you select "Checkbox" as the Column Type. The following formula, it true, should then mark the Checkbox.


    =IF(AND([Rehab?]1 ="Yes", [Xtra Cost (cog, rehab, tx, etc.)]1 = 0), 1, 0))


    Hope this helps.

  • 15UZU
    15UZU ✭✭



    Please be aware "" = NULL which is not the same as using a zero to indicate a blank. NULL is litterally "no value", whereas a 0 is actually still a valid number value value for a text/number data type.


    Best example i can give you is if you have a "true"/"false" trigger - those two values are also represented as 0 & 1. If you return a 0 instead of a NULL as a part of a non-trigger event, you would actually send a "false" trigger instead of "no trigger"

  • Joshua Rose

    Good point, 15UZU! And thanks Gerhard!

This discussion has been closed.