Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Harvey Ball formula that reads across and produces cumulative


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.




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


This discussion has been closed.