If/Then formula for symbols based on range?

Dana G

Hello,

I'm collecting responses from multiple departments for the same question. I've added the question to a parent row, and each department is a child row below it. Status is a ball symbol...red=issue, yellow=in progress, and green=complete...and every row has a symbol.

I'd like to create a formula on the parent row based on the child rows. So, if any child row has a red ball, the parent row will have a red ball. If child rows have no red but at least one yellow, the parent row will have a yellow status. And finally, the parent row will only show green status if ALL child rows are green.

I'm not sure where to even start with this formula! Any help is appreciated :)

Thank you!

Genevieve P.

You're welcome! I'm glad I could help 🙂

Genevieve P.

Hi @Dana G

You can use the CHILDREN function to COUNT how many of the Child rows have a certain colour, then display the correct colour in the Parent row. Try something like this:

=IF(COUNTIF(CHILDREN(), "Red") >= 1, "Red", IF(COUNTIF(CHILDREN(), "Yellow") >= 1, "Yellow", "Green"))

See: COUNTIF Function / CHILDREN Function / IF Function

Cheers,

Genevieve

Dana G

Thank you @Genevieve P. ! This formula is so elegant :) it worked perfectly.

Genevieve P.

You're welcome! I'm glad I could help 🙂