Traffic lights count by colour if children

louisebarren
louisebarren ✭
edited 12/09/19 in Formulas and Functions

Hi there,

I'm trying to count the number of projects according to their traffic light status across about 7 sheets. Given I want to display this in Sights as a Metric, I can't use a report so I have a sheet linking to the summary information from each sheet. When I use the Countif formula to count those that are "Red" it double counts any parent rows as there is a formula saying if a child is red, the parent also needs to be red etc. I can't seem to work out how to only count the children or to remove any rows that are parents.

I would also like work out a trend for how this program is tracking again based on traffic light status. If we have 8 that are red, 10 yellow and 55 green, overall is there a formula that weights red more than yellow and yellow more than green to state if the project is on track or not overall?

 

Thanks in advance

Louise

Tags:
· Share on FacebookShare on Twitter

Comments

  • Shaine Greenwood
    Shaine Greenwood Employee

    Hi Louise,

    Since you're cell linking values to another sheet, then displaying the values from that sheet as a metric, you'll need to manually delete the links that you have to parent rows. You can do that by going to the source sheets, hovering over the parent rows that are cell linked, then clicking delete.

    You may also consider adding weight manually in the formula directly. For instance:

    =COUNTIF(Status1:Status15, "Red") * 5 

    =COUNTIF(Status1:Status15, "Yellow") * 3

    =COUNTIF(Status1:Status15, "Green")

    This will add a weight to the reds and yellows. This is an arbitrary weight to give you an example.

    · Share on FacebookShare on Twitter
  • louisebarren
    louisebarren ✭

    Hi Shaine,

    Thanks - I have deleted the parent rows.

    However, for the overall trend I am wanting this to look at the status for all the projects and tell me the overall status at that point in time rather than just weighting one colour at a time. Any ideas of this can be done - and this may still require them to be weighted and then work out the trend but I can't work out how to do this.

     

    Louise 

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!