Combine 2 formulas with an or function

Wouter Hels

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"))

Eric Law

@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

@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")))

Wouter Hels

@Eric Law Thanks for the help. Your formula works perfect.