Need text response in cell if criteria are met or not.
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
Best Answer

To check for the presence of one or more red X symbols in the 6 columns, you can use the
COUNTIF
function. TheCOUNTIF
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

To check for the presence of one or more red X symbols in the 6 columns, you can use the
COUNTIF
function. TheCOUNTIF
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. 
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
Categories
Check out the Formula Handbook template!