Applying IF(ISBLANCK to more columns

Anne Santini

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

CE Abandoned_screenshot.jpg

CE Started_screenshot.png

Nick Korna

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! 😊

Nick Korna

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

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

No problem, happy to have helped! 😊