Combine 2 formulas with an or function

Options

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

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!