Formula for Counting RYG by Child Row

Staci Britton

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.

L_123

Alright, I understand you now.

The easiest way to do this is to add a helper column on your first sheet. Then use this formula and drag it all the way down.

=iferror(parent([initiative / milestone details]@row),"")

Then you can use your countifs

=countifs({Helper},[Primary Column]@row,{Status},"Red")

or something like that.

L_123

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

Staci Britton

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

something like this?

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

Staci Britton

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!!

L_123

Alright, I understand you now.

The easiest way to do this is to add a helper column on your first sheet. Then use this formula and drag it all the way down.

=iferror(parent([initiative / milestone details]@row),"")

Then you can use your countifs

=countifs({Helper},[Primary Column]@row,{Status},"Red")

or something like that.

Staci Britton

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

Staci Britton

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

L_123

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

It's not reading these rows...

L_123

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