Combine 2 formulas with an or function
Hi all,
I have 2 working formulas that I want to merge with an or function. Either formula 1 or formula 2.
Formula 1 has Green as result if first 9 chexboxes are checked,gives red if no box is checked, has yellow if some boxes are checked.
Formula 2 gives blue as the result if all boxes have been checked and has yellow if some have been checked and gives red if none have been checked.
I am rather new to writing these types of formulas. How can I put this formula together so that either formula1 has the correct answers or formula 2 has the correct answers.
Formula 1(it should only consider The first 9 check boxes) =IF(COUNTIF([ADDED CONTACT LIST SMARTHEET]@row:[Intake doc uploaded]@row; 1) = 9; "Green"; IF(COUNTIF([ADDED CONTACT LIST SMARTHEET]@row:[Intake doc uploaded]@row; 0) = 9; "Red"; "Yellow"))
Formula 2(this must take into account 11 check boxes(é extra boxes for contractors)): =IF(COUNTIF([ADDED CONTACT LIST SMARTHEET]@row:[Cooperation agreement uploaded]@row; 1) = 11; "Blue"; IF(COUNTIF([ADDED CONTACT LIST SMARTHEET]@row:[Cooperation agreement uploaded]@row; 0) = 11; "Red"; "Yellow"))
Best Answer

@Wouter Hels You don't need an or formula, just embedded IFs. Here's an easier way to think of it.
Blue if all boxes checked
Green if first 9 are checked
Yellow if at least 1 is checked
Red if none are checked
Can't really help you with the formula as I can't really read what they say but replace Column1, Column9 and Column11 with the proper column names and it will work.
=IF(COUNTIF([Column1]@row:[Column11]@row,1) = 11, "Blue", IF(COUNTIF([Column1]@row:[Column9]@row,1)=9, "Green", IF(COUNTIF([Column1]@row:[Column11]@row,1)=0, "Red", "Yellow")))
Answers

@Wouter Hels You don't need an or formula, just embedded IFs. Here's an easier way to think of it.
Blue if all boxes checked
Green if first 9 are checked
Yellow if at least 1 is checked
Red if none are checked
Can't really help you with the formula as I can't really read what they say but replace Column1, Column9 and Column11 with the proper column names and it will work.
=IF(COUNTIF([Column1]@row:[Column11]@row,1) = 11, "Blue", IF(COUNTIF([Column1]@row:[Column9]@row,1)=9, "Green", IF(COUNTIF([Column1]@row:[Column11]@row,1)=0, "Red", "Yellow")))

@Eric Law Thanks for the help. Your formula works perfect.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!