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!

Find more posts tagged with

Accepted answers

Mark Cronk

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

All comments

Mark Cronk

Hi Christi,

Try:

Mark

Christie Delbrey

Thanks Mark! It worked perfectly. It never occurred to me to use the COUNTIF fx like that. I will surely apply in other sheets.

Mark Cronk

Glad you found a solution. Thank you for contributing to the Community.

Mark

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.