What formula do I use to determine a value from multiple rows?

JocelynF

I have 6 centers. Each center has its own row and a status of On Track, Off Track, or High Risk. If all centers are On Track, the organization is On Track If one center is Off Track or High Risk, the whole organization is Off Track or High Risk. I need a way to change the status in a separate column if any of the six centers (all on different rows) change their status. My best guess was to start the formula this way (trying to make it apply to just two of the centers to start, but I'm ultimately lost. Any help would be greatly appreciated!

IF(OR([Center Crucial Time Status]1 = "Off Track,"([Center Crucial Time Status]2="OffTrack,"), "Off Track"))

KPH

Hi @JocelynF

You are off to a good start but using IF and OR is going to get out of hand very quickly. You need to work with the range in the Center Crucial Time column rather than individual cells. You can do this using COUNTIF. So you count how many cells in the column are at a particular status and then use that number in the IF.

=COUNTIF([Center Crucial Time Status]:[Center Crucial Time Status], "High Risk")

will count how many centers are High Risk.

=COUNTIF([Center Crucial Time Status]:[Center Crucial Time Status], "Off Track")

will count how many centers are Off Track.

You can use those in the IF.

=IF(COUNTIF([Center Crucial Time Status]:[Center Crucial Time Status], "High Risk")>0,"High Risk")

this will return High Risk if the count of cells that are High Risk is greater than zero.

Then you can add another IF to be evaluated if the count is not greater than zero.

=IF(COUNTIF([Center Crucial Time Status]:[Center Crucial Time Status], "High Risk")>0,"High Risk", IF(COUNTIF([Center Crucial Time Status]:[Center Crucial Time Status], "Off Track")>0,"Off Track"))

this will return High Risk if the count of cells that are High Risk is greater than zero, if it is not and the count of cells that are "Off Track" is greater than 0 it will return "Off Track".

Finally, you can add "On Track" to be returned if neither of the counts are greater than 0, which must mean all are "On Track".

=IF(COUNTIF([Center Crucial Time Status]:[Center Crucial Time Status], "High Risk") > 0, "High Risk", IF(COUNTIF([Center Crucial Time Status]:[Center Crucial Time Status], "Off Track") > 0, "Off Track", "On Track"))

This will work regardless of how many centers you have in the column.

Hope that works for you.