Applying IF(ISBLANCK to more columns
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
-
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
-
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! 😊
-
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
-
No problem, happy to have helped! 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!