USE OF ISBLANK FUNCTION WITH HAS FUNCTION IN SAME FORMULA
Hi,
I desperately need help to figure out the use of the ISBLANK function jointly with other functions. I'm fairly new using it. I have a table that has three possible values, Yes, No or the cell is still blank as we are filling the table. The purpose of the table is to track the result of an inspection in a project. Therefore, I would like the Result column to return "Not Approved" if any of the inspection parameters is marked as "No" or to return "Approved" if all the inspection parameters indicate "Yes". If a given cell is blank (I selected one specific column as the indicator), then I want the result column to return "Pending Inspection".
I have managed the formula to work separately to return "Not Approved" or "Approved" using this formula:
=IF(HAS([Inspection Criteria 1]@row:[Inspection Criteria 10]@row, "No"), "Not Approved", "Approved")
Then, separately, I have managed the ISBLANK function to work with this formula:
=IF(ISBLANK([Inspection Criteria 1]@row), "Pending Inspection")
I need help combining the two formulas for each row to return the proper value that is also reflected on my dashboard.
Appreciate any help!
Best Answer
-
Hi Christi,
Try:
=IF(COUNTIF([Inspection Criteria 1]@row:[Inspection Criteria 10]@row, "No")>0,"Not Approved", IF(COUNTIF([Inspection Criteria 1]@row:[Inspection Criteria 10]@row, "Yes")=10, "Approved", "Pending Inspection"))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi Christi,
Try:
=IF(COUNTIF([Inspection Criteria 1]@row:[Inspection Criteria 10]@row, "No")>0,"Not Approved", IF(COUNTIF([Inspection Criteria 1]@row:[Inspection Criteria 10]@row, "Yes")=10, "Approved", "Pending Inspection"))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Thanks Mark! It worked perfectly. It never occurred to me to use the COUNTIF fx like that. I will surely apply in other sheets.
-
Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!