USE OF ISBLANK FUNCTION WITH HAS FUNCTION IN SAME FORMULA

Christie Delbrey

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!

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

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

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