Formula to show specific symbols based on multiple criteria

Chris Pollock

Hello, I hope some of you fine folk might be able to help me.

I am tracking the results of three separate tests and I want to create a column that will show either a Green, Amber or Red based on the results.

See screenshot below. There is a checkbox to highlight if a test has been completed and then a dropdown to show if it was a pass, fail or to be discussed.

Then in the Flag Column I want to to show a symbol based on the results within the three Pass/Fail Columns as is currently shown but was completed manually:

I have manage to get the below formula to work that indicates the status of a row based on the percentage of tests complete and the due date for them to be completed:

=IF([Parent?]@row = 1, IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Blue") > 0, "Blue", "Green"))), IF([% Completed]@row = 1, "Green", IF(AND(Finish@row < TODAY(), [% Completed]@row < 1), "Red", IF([% Completed]@row = 0, "Blue", "Yellow"))))

Using this I have tried to change the formula to do what I require for the Pass/Fail flag, but I'm struggling to figure out what I need to change.

Any help you could provide would be hugely appreciated!

Paul Newcome

Try this...

=IF(CONTAINS("Fail", [Test 1 Pass/Fail]@row:[Test 3 Pass/Fail]@row), "No", IF(CONTAINS("Discuss", [Test 1 Pass/Fail]@row:[Test 3 Pass/Fail]@row), "Hold", "Yes"))

Paul Newcome

How would you want the pass/fail to impact the outcome?

Chris Pollock

Hi Paul,

Sorry I should have been more clear. I want the flag to show Green is there is nothing but passes shown, Amber if one of the tests shows 'Discuss' and Red if any one shows 'Fail'

If there is a blank it should ignore and only look at the cells that have results input.

I can make this work if just looking at one Test result, but I'm struggling to figure out how to make it work when it takes all three results into account.

Paul Newcome

And you are wanting this on the parent row, and you want it to incorporate all three pass/fail columns in the one symbol column?

Chris Pollock

The Parent row isn't important, I can get that to show a symbol based on the children rows, but yes for the incorporating all three columns into one symbol column.

Paul Newcome

Try this...

=IF(CONTAINS("Fail", [Test 1 Pass/Fail]@row:[Test 3 Pass/Fail]@row), "No", IF(CONTAINS("Discuss", [Test 1 Pass/Fail]@row:[Test 3 Pass/Fail]@row), "Hold", "Yes"))

Chris Pollock

Paul, that works perfectly thank you.

I spent absolutely ages trying out huge lines of formula using IF AND, but never once did it occur to me to use CONTAINS.

Really appreciate your help with this!

Paul Newcome

Happy to help. 👍️

The only reason this works though is because even though we are including the checkbox columns within our CONTAINS function, we know that those columns will never contain any of those three values. If you even insert a column inside of that range that could contain one or more of those three values but you wanted it excluded from the calculation then we would have to rewrite the formula a bit.