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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!