How do I assign a color symbol based on criteria from 2x data ranges?

Colbot

We have a critical check box column (mark a task as critical) and then separately we have a R/G/Y column for task status. Critical & Status are not intrinsically linked and you can have a task in the red that isn't critical.

My goal has been to have the symbol column in the parent row change based on if critical is checked & status is red. Basically, I want to say if any of our critical tracks are in the red, the project goes red so we can review. I've tried IF(AND / IF(AND(CONTAINS and nested IF but it gives me an error every time.

I can achieve the result I'm looking for if I do one row of data at a time, but when I reference the columns (Crit and R/G/Y respectively) the formula breaks.

Are you not able to do the type of action I'm trying to perform or is this user error on my end?

Ex Formula:

=IF(AND({Sprint Schedule Range 1} = 1, {Sprint Schedule Range 2} = "Red"), "Red", "Yellow")

I've tried a few variations and I'm at a loss, any advice would be greatly appreciated.

Kelly Moore

Hey @Colbot

I'm happy to help. To clarify, is this formula actually trying to evaluate data on a different sheet, or is all of your data contained within this current formula sheet? The syntax of the formula changes depending if you're pulling data from another sheet and in the formula above you have mixed the two scenarios. You also mention that you have Child and Parent rows. For the Status of the Child rows - are you manually updating that status or is the Status determined by a formula? Is it possible to see a screenshot of the sheet(s) (sensitive info removed). Assuming you are working between two sheets, I'm looking for a common column like Project ID, etc that will link information from one sheet to another.

This formula is if all the information is within this same sheet, this will go into the Parent row Status columns. If your Child rows have their own formula, we can combine the two with a small amount of tweaking.

=IF(COUNTIFS(CHILDREN([Critical Task]@row), 1, CHILDREN(Status@row), "Red")>0, "Red", "Yellow")

*You will need to adjust the column names in the formula above to your actual column names.

If the data is being pulled from another sheet, let me know and we will adjust the formula to the correct syntax.

Kelly