Applying IF(ISBLANCK to more columns

Options

Hello,

I saw other similar questions but couldn't see any responses that fit my situation. In the column "Started 1 or more activities," I would like to insert a formula that writes "yes" in that column if the student is enrolled in any activity described in the following columns, and "no" if cells in the following columns are blank.

As can be seen in the attached "CE Started_screenshot" file, the IF(ISBLANCK(etc formula works for one cell but I can't get it to work for a series of columns. I am attaching instead the screenshot of another column where, in order to indicate students who abandoned 1 or more activities, I used a IF(CONTAINS(etc. formula, which, in that case, I was able to apply to 9 columns and that works just fine. Is something similar not possible with IF(ISBLANCK?

The reason why I want an answer in this column is to then be able to use COUNT and extract quantifiable results for analytical and statistical purposes.

Many thanks for your help!

Anne



Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Anne Santini,

    ISBLANK can't handle ranges unfortunately, so you'd have to used a long AND statement if you wanted to get the count that way.

    However, you could do it much easier by using either an IF and either CONTAINS/COUNTIF formula to find out the ones which qualify for yes and then the remainder being "no".

    For example, if you only want to find "Enrolled" then this should work:

    =IF(CONTAINS("Enrolled", [Angeli del Bello]@row:[Intern with Magenta publishing]@row), "Yes", "No")

    If you wanted to include both "Enrolled" and "Abandoned":

    =IF(OR(CONTAINS("Enrolled", [Angeli del Bello]@row:[Intern with Magenta publishing]@row), CONTAINS("Abandoned", [Angeli del Bello]@row:[Intern with Magenta publishing]@row)), "Yes", "No")

    For comparison, the formula using ISBLANK & COUNTIF is much longer:

    =IF(AND(ISBLANK([Angeli del Bello]@row), ISBLANK([Conversation Exchange Gobetti HS]@row), ISBLANK([Teaching Support MS Spanish]@row), ISBLANK([Teaching Support MS English]@row), ISBLANK([Teaching Support HS]@row), ISBLANK([Women's Shelter]@row), ISBLANK([Sipario social cooperative]@row), ISBLANK([Ars et Fides]@row), ISBLANK([Intern with Magenta publishing]@row)), "No", IF(COUNTIF([Angeli del Bello]@row:[Intern with Magenta publishing]@row, "Enrolled") >= 1, "Yes", ""))

    Results:

    Hopefully this is of some use to you; if you've any questions or comments then just post! 😊

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Anne Santini,

    ISBLANK can't handle ranges unfortunately, so you'd have to used a long AND statement if you wanted to get the count that way.

    However, you could do it much easier by using either an IF and either CONTAINS/COUNTIF formula to find out the ones which qualify for yes and then the remainder being "no".

    For example, if you only want to find "Enrolled" then this should work:

    =IF(CONTAINS("Enrolled", [Angeli del Bello]@row:[Intern with Magenta publishing]@row), "Yes", "No")

    If you wanted to include both "Enrolled" and "Abandoned":

    =IF(OR(CONTAINS("Enrolled", [Angeli del Bello]@row:[Intern with Magenta publishing]@row), CONTAINS("Abandoned", [Angeli del Bello]@row:[Intern with Magenta publishing]@row)), "Yes", "No")

    For comparison, the formula using ISBLANK & COUNTIF is much longer:

    =IF(AND(ISBLANK([Angeli del Bello]@row), ISBLANK([Conversation Exchange Gobetti HS]@row), ISBLANK([Teaching Support MS Spanish]@row), ISBLANK([Teaching Support MS English]@row), ISBLANK([Teaching Support HS]@row), ISBLANK([Women's Shelter]@row), ISBLANK([Sipario social cooperative]@row), ISBLANK([Ars et Fides]@row), ISBLANK([Intern with Magenta publishing]@row)), "No", IF(COUNTIF([Angeli del Bello]@row:[Intern with Magenta publishing]@row, "Enrolled") >= 1, "Yes", ""))

    Results:

    Hopefully this is of some use to you; if you've any questions or comments then just post! 😊

  • Anne Santini
    Options

    Hi @Nick Korna !

    Thank you for your very exhaustive and helpful response. I just tested your "IF(OR(CONTAINS" formula and it works great! Many thanks for such prompt and efficient assistance.

    Anne

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    No problem, happy to have helped! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!