Harvey Ball formula that reads across and produces cumulative

JDigrazia

Hi All - 

I would like a formula that reads across vs vertically - here is my vertical one.  I need the same thing but read about 10 columns status to then come up with an overall status meaning if there is 1 red, 7 yellow and 2 green then the overall cumulative status is red (lowest common denominator)

Vertical Formula:

=IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", IF(COUNTIF(CHILDREN(), "Blue") > 0, "Blue", " "))))

Horizontal Formula????

Shaine Greenwood

You'll need to type out the column range to reference cells to the right or left. For example:

=COUNTIF([Task Name]1:Status1, "Red")

More on creating formulas can be found in our Help Center: https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet

JDigrazia

This only counts the number of reds across the row.  

What I need the formula to do is, look at each row, if there are any red anywhere on the row, then the overall status in the last column is represented by a red harvey ball.

Shaine Greenwood

The formula you initially provided has the correct comparison logic, you just need to remove every instance of the CHILDREN() function and replace it with a cell range that spans from the first cell to the last cell in the row you're trying to count/change the RYG ball on.

Productforce1:Services1

 

 

scbirdsong

I'm trying to do the same thing but continue to get #unparseable. 

 

I want to look across the same row at all RYGG statuses and drop in the lowest status in the first column (column2). For example, if any of the statuses on the row is 'Red', the overall should be red. I tried changing the cell reference to include commas since two of the cells are free text, not RYGG. I also tried w/out the @row.

 

=IF(COUNTIF((Column7:Column12)@row"Red") > 0, "Red", IF(COUNTIF((Column7:Column12)@row"Yellow") > 0, "Yellow", IF(COUNTIF((Column7: Column12)@row"Green") > 0, "Green", IF(COUNTIF((Column7:Column12)@row"Gray") > 0, "Gray", "NONE"))))