Formula for Counting RYG by Child Row

Options

Hello - I'm trying to add to a Dashboard. I need to see status by color for each child row. I also need a count by each child row (Admin, CPM, etc.). Help! Thank you.

Best Answer

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    You mean you want each child to show on the dashboard? Do you have your information posted to a report?

  • Staci Britton
    Options

    I only want to see the status (red, yellow, green, or gray) for each child row. One child row might have one entry with the status as yellow. Additional child rows might have rows with three reds, and or 10 greens by row. I'm trying to reference a sheet as well. I tried this but I'm sure I'm way off:

    =COUNTIFS(CHILDREN(){QSP Initiatives Range 1}, Red2, {QSP Initiatives Range 1}, Yellow2, {QSP Initiatives Range 1}, Green2, {QSP Initiatives Range 1}, Grey2))

    Thanks for your help!

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 06/15/20
    Options

    something like this?


    ="Green: " + countif(children(),"Green ")+"Yellow: " + countif(children(),"Yellow"),+"Red: " + countif(children(),"Red") + "Grey: " + countif(children(),"Grey")

  • Staci Britton
    Options

    I've used this formula:

    =COUNTIFS({Level}, Red$1, {Level}, Yellow$1, {Level}, Green$1, {Level}, Grey$1)

    I'm getting #s on my destination sheet but not the correct numbers from status column on the reference sheet.


    Reference Sheet:

    Destination Sheet:

    If you see Admin, it has one yellow - that does not appear under Admin-Yellow. CPM has at least two green and a grey - they do not appear on the CPM line under green or grey.

    I think I'm close but don't know what I'm missing.

    Thank you for your help L@123!!

  • Staci Britton
    Options

    THANK YOU!!!!! That worked!! Whooop!

  • Staci Britton
    Options

    It's actually not reading the collapsed rows. How would I fix this?

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    Right click the primary column and select "Expand all". Then drag your helper formula down across all items. Your helper column wasn't copied to rows you dragged it across if they were collapsed. You can them re-collapse your rows and it should work.

  • Staci Britton
    Options

    It's not reading these rows...

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    Do you have the exact same spelling/spacing in the primary column in your lookup sheet?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!