calculating status based on status of multiple columns

Oren Tauber Sharon

Hi,

I am trying to calculate the status of a main column ("status") based on the status of multiple columns (OP2-OP8).

The idea is to automatically change the status (on the left) from "Open" to "Complete" based on the individual status of the columns on the right (Open/Closed).

Background: we try to monitor the issue status of each line based on the progress of the issue per each OP. e.g. if the issue is open in at LEAST one OP the status stays open. ONLY if all four are CLOSED that entitles the main status to change to Complete.

The complexity is, that a certain issue is not necessarily applicable to all OPS (see Affected_OPs column).

Any ideas how to build a function around that?

Many thanks

Oren

Mike Wilday

I think there might be several logical approaches. The way I would probably do it is to use an IF Formula that counts all Opens in in the cells before it and flags it when there aren't any.

Try:

=IF(Countif(OP1@row:OP8@row., "Open") = 0, "Closed",. "Open")

https://help.smartsheet.com/function/countif

Mike Wilday

I think there might be several logical approaches. The way I would probably do it is to use an IF Formula that counts all Opens in in the cells before it and flags it when there aren't any.

Try:

=IF(Countif(OP1@row:OP8@row., "Open") = 0, "Closed",. "Open")

https://help.smartsheet.com/function/countif