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

Find more posts tagged with

Accepted answers

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.