Sheet with Single Row needs formula across the row?

I have a sheet of indicators that pulls info from another sheet to process the correct indicator. I now need to set an overall status in the same row from the other indicators

Here is the sheet and it will always look like this just one row

So the second column Program needs to have a formula that looks at all the other items in the row to set the indicator,

If any of them are red the program status should be red, if any are yellow the program status should be yellow or it is green.

Since this was one row I did not thing a count function applied,

If was writing code I would say

If Schedule or Scope or Budget or .... is Red than Red

Else If Schedule or Scope or Budget or .... is Yellow than Yellow

Else Green.

Can I define this like this

If([Schedule] Or [Scope] Or..... = Red, Red, IF([Schedule] Or [Scope] Or..... = yellow, Yellow, Green))

Caroline

Find more posts tagged with

Formulas

Accepted answers

Kleerfyre

Try a nested IF formula.

=IF(Schedule@row = "Red", "Red", IF(Scope@row = "Red", "Red", IF(Budget@row = "Red", "Red", IF(Infrastructure@row = "Red", "Red", IF(Data@row = "Red", "Red", IF(Resources@row = "Red", "Red", IF(Schedule@row = "Yellow", "Yellow", IF(Scope@row = "Yellow", "Yellow", IF(Budget@row = "Yellow", "Yellow", IF(Infrastructure@row = "Yellow", "Yellow", IF(Data@row = "Yellow", "Yellow", IF(Resources@row = "Yellow", "Yellow", "Green"))))))))))))

All comments

Kleerfyre

Try a nested IF formula.

Genevieve P.

There are many ways to do this, and I fully agree with @Kleerfyre!

To throw an alternative out there... you could use a COUNTIF statement to count how many times a Red appears in the row, and if it's greater than 1 return "Red", and so on.

For example:

=IF(COUNTIF(Schedule@row:Resources@row, "Red") > 0, "Red", IF(COUNTIF(Schedule@row:Resources@row, "Yellow") > 0, "Yellow", "Green"))

Cheers,

Genevieve

delaurellc

Johnathan,

Perfect thank you!

Caroline

Kleerfyre

@Genevieve P. that's a good point too! Like your cleaner formula. Didn't think about the COUNTIF function for this.

Carson Penticuff

And just to show there are even more options:

=IF(CONTAINS("Red", [Schedule]:[Resources]), "Red", IF(CONTAINS("Yellow", [Schedule]:[Resources]), "Yellow", "Green"))

delaurellc

They both work, this is great!

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.