Cell Formula For Traffic Light Symbols

Hello, see attached screenshot. in the yellow cell (top right) I am wanting to have a SUM of the below 'Days To Complete' cells, but only those that have the Green traffic lights.

As you can see this is a team members project list, so we are trying to estimate the work ahead for this team member.

Can someone please help with a formula to do this?


Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Trent Simpkin

    The CHILDREN function will want you to reference the Parent Row Cell, versus selecting a range. It will create the range for you!

    Try copying/pasting exactly this:

    =SUMIF(CHILDREN(Status@row), "Green", CHILDREN())


    In the second Children reference, you don't need to add any column names or rows at all. If you leave it as () then it will know to look at the children of this column. Make sense?

    See: CHILDREN Function

    Cheers!

    Genevieve

Answers

  • J Tech
    J Tech ✭✭✭✭✭

    The simplest option would be to put it in the status column parent row:

    =COUNTIF(CHILDREN(), "Green")

    Regards

    J Tech

    If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
  • Thanks J Tech, however I am wanting a sum of the numbers to the right of the green cells, as attached?


  • Genevieve P.
    Genevieve P. Employee Admin
    edited 03/15/22

    Hi @Trent Simpkin

    You can use the same principle but within a SUMIF statement! (See: SUMIF Function)

    Try this:

    =SUMIF(CHILDREN([Symbol Column]@row), "Green", CHILDREN())

    You'll need to reference the column name that contains the symbols in your first Children function so the formula know what column contains Green Children.

    Then the second CHILDREN() tells the formula what child rows to SUM... the ones in this current column.

    Cheers,

    Genevieve

  • Thanks Genevieve, however its giving me a result of 0?

    I don't feel like i've got it quite right. See below with row numbers and column names.

    Really appreciate the help!


  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Trent Simpkin

    The CHILDREN function will want you to reference the Parent Row Cell, versus selecting a range. It will create the range for you!

    Try copying/pasting exactly this:

    =SUMIF(CHILDREN(Status@row), "Green", CHILDREN())


    In the second Children reference, you don't need to add any column names or rows at all. If you leave it as () then it will know to look at the children of this column. Make sense?

    See: CHILDREN Function

    Cheers!

    Genevieve

  • Thanks Genevieve, works perfectly!

  • Ronny Dorian
    Ronny Dorian ✭✭✭✭

    Hello

    What if you had a formula where the output is some color, "Green", "Yellow", etc., and you wanted that output to be represented as a traffic light in one cell, how would you do that?

    Similar to if you had a Text column, but you wanted one cell within it to output a date format, you would use DATEONLY in the beginning of its formula. How do you do the same for a traffic light symbol?

    Thanks, Ronny

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Ronny Dorian

    Symbols can only appear if the type of column is a Symbol column. If you want a symbol in a Text column, you could use a formula to output an emoji, instead!

    For example:

    =IF(this@row = "Green", "🟢")

    Cheers,

    Genevieve

  • Ronny Dorian
    Ronny Dorian ✭✭✭✭

    That'll do! Thanks Genevieve!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!