Any professor types out there that are up for an impossible challenge?

I need to RAG overall section 1 (row 7) based on the completeness of the subsections 1.1 to 1.6.

So,

section 1 would turn red if 1.1 to 1.6 have anything in the "no" box

section 1 would turn red/amber if two of 1.1 to 1.6 have anything in the "yes" box

section 1 would turn amber if three or four of 1.1 to 1.6 have anything in the "yes" box

section 1 would turn amber/green if five of 1.1 to 1.6 have anything in the "yes" box

section 1 would turn green if all 1.1 to 1.6 have anything in the "yes" box

The doozy - if the N/A is ticked, this row needs taken out of the equation or classed as a "yes" for the purposes of RAG


impossible example.png