Pass Fail criteria based on multiple cell colour conditional formatting

[veedub1955]

Hi I am trying to do an overall pass criteria based on multiple conditions of cell colours. I've already set the conditional formatting to turn the single cell red or green based on the cell numerical value, I now want to do the following:-

In one column of 10 cells the top 5 cells are allowed a maximum of one red failed cell in the range and the bottom 5 cells are allowed a maximum of two red fails for the whole column data to pass the test criteria. To report the result I want to turn a different cell red or green based on this criteria

I then have an additional overall pass fail in that I cannot have one failed column as above, but I can sort that once I know the solution to the column fail

Thanks a lot

Nick

 

[veedub1955]

Sorry forgot to add im using excel 2016