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
Check out the Formula Handbook template!