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

SSParks

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

David Jasven

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.

David Jasven

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

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