Formula If range of rows status is checked

KSPP · 01/27/25

Hi. I want to use a formula to determine the status symbol when all rows in a certain range are checked complete.

Below is what I have right now, but it is not correct. I am new to SmartSheet ;)

=IF(Status2:Status5 = "true", "Green", "Gray")

Katy H · 01/27/25

Ah! I misread your initial question, please see two options below.

If Status2 - Status5 are all child rows, an easy formula would be:

=IF(COUNTIF(CHILDREN(Status@row), 1) = COUNT(CHILDREN(Status@row)), "Green", "Gray")

If however, they aren't child rows, this would be the formula to use to get a specific range.

=IF(COUNTIF(Status2:Status5, 1) = COUNT(Status2:Status5), "Green", "Gray")

Katy H · 01/27/25

Try =IF(Status2:Status5 = 1, "Green", "Gray")

Also, what error are you getting when you input that formula? There could be other issues at play.

KSPP · 01/27/25

#INVALID OPERATION

=IF(Status2:Status5 = 1, "Green", "Gray") gave #INVALID OPERATION as well.

Status column is Checkbox

KSPP · 01/27/25

I am trying to get the status symbol to change to green when all rows in a designated range (the subitems) are checked off.

Katy H · 01/27/25

Ah! I misread your initial question, please see two options below.

If Status2 - Status5 are all child rows, an easy formula would be:

=IF(COUNTIF(CHILDREN(Status@row), 1) = COUNT(CHILDREN(Status@row)), "Green", "Gray")

If however, they aren't child rows, this would be the formula to use to get a specific range.

=IF(COUNTIF(Status2:Status5, 1) = COUNT(Status2:Status5), "Green", "Gray")

KSPP · 01/27/25

=IF(COUNTIF(CHILDREN(Status@row), 1) = COUNT(CHILDREN(Status@row)), "Green", "Gray")

This is perfect! Worked! Thank you so much!

Best Answer