Traffic lights count by colour if children

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



  • 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.

  • 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.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!