Need text response in cell if criteria are met or not.

Options

Greetings-

I have 6 columns with symbols. I have a working formula if all 6 columns have a green check the column before shows the words "All Approved" and the cell goes green. This works just fine. What I cant get to work is if one of the columns has a red X or more then one red X the first column reads "Denied" (and the cell goes red). Below is my working formula for the green checks:

=IF([ENT Status]@row = "Yes", IF([ED Status]@row = "Yes", IF([NR Status]@row = "Yes", IF([OPR Status]@row = "Yes", IF([P&P Status]@row = "Yes", IF([Safety Status]@row = "Yes", "All Approved"))))))

Thank you for any help! MD

Tags:

Best Answer

  • David Jasven
    David Jasven ✭✭✭✭
    Answer ✓
    Options

    To check for the presence of one or more red X symbols in the 6 columns, you can use the COUNTIF function. The COUNTIF function allows you to count the number of cells in a range that meet a certain condition.

    Here's an example of how you can use the COUNTIF function to check for the presence of red X symbols in the 6 columns:

    Copy code
    =COUNTIF([ENT Status]@row: [Safety Status]@row, "X") 
    

    This formula will count the number of cells in the range [ENT Status]@row: [Safety Status]@row that contain the value "X".

    You can then use this count in an IF statement to determine whether there are any red X symbols in the 6 columns. Here's an example of how you can modify your formula to check for the presence of red X symbols:

    Copy code
    =IF(COUNTIF([ENT Status]@row: [Safety Status]@row, "X") > 0, "Denied", IF([ENT Status]@row = "Yes", IF([ED Status]@row = "Yes", IF([NR Status]@row = "Yes", IF([OPR Status]@row = "Yes", IF([P&P Status]@row = "Yes", IF([Safety Status]@row = "Yes", "All Approved"))))))) 
    

    This modified formula will first check if there are any red X symbols in the 6 columns using the COUNTIF function. If there are, it will return "Denied". If there are not, it will proceed to check for green check marks in the 6 columns as your original formula did.

Answers

  • David Jasven
    David Jasven ✭✭✭✭
    Answer ✓
    Options

    To check for the presence of one or more red X symbols in the 6 columns, you can use the COUNTIF function. The COUNTIF function allows you to count the number of cells in a range that meet a certain condition.

    Here's an example of how you can use the COUNTIF function to check for the presence of red X symbols in the 6 columns:

    Copy code
    =COUNTIF([ENT Status]@row: [Safety Status]@row, "X") 
    

    This formula will count the number of cells in the range [ENT Status]@row: [Safety Status]@row that contain the value "X".

    You can then use this count in an IF statement to determine whether there are any red X symbols in the 6 columns. Here's an example of how you can modify your formula to check for the presence of red X symbols:

    Copy code
    =IF(COUNTIF([ENT Status]@row: [Safety Status]@row, "X") > 0, "Denied", IF([ENT Status]@row = "Yes", IF([ED Status]@row = "Yes", IF([NR Status]@row = "Yes", IF([OPR Status]@row = "Yes", IF([P&P Status]@row = "Yes", IF([Safety Status]@row = "Yes", "All Approved"))))))) 
    

    This modified formula will first check if there are any red X symbols in the 6 columns using the COUNTIF function. If there are, it will return "Denied". If there are not, it will proceed to check for green check marks in the 6 columns as your original formula did.

  • SSParks
    SSParks ✭✭✭✭
    Options

    Exactly what I needed. Thank you for the time and the help! Only change I had to make in order to make it work was to change your "X" to "NO" (In case anyone else needs this formula)

    =IF(COUNTIF([ENT Status]@row:[Safety Status]@row, "No") > 0, "Denied", IF([ENT Status]@row = "Yes", IF([ED Status]@row = "Yes", IF([NR Status]@row = "Yes", IF([OPR Status]@row = "Yes", IF([P&P Status]@row = "Yes", IF([Safety Status]@row = "Yes", "All Approved")))))))

    Thank you! MD

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!