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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    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.

  • 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
    Mark Cronk ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!