Excel formula request and help

Hi,

Having trouble with a forumla i need help with.

We are tracking students work and if it has been completed. However, i need an IF formula where if the student has not complete work for 4 subjects then they flag up.

At the end of the sheet i have put in columns to show a number for the amount of reds and greens

I have completed conditional format to type red(not handed in work) type green(completed) amber (ok work)

But overall i need to know...

From Week commencing show the subject not completed work for that child.

So IF red shows for the child, then show subject for that week commencing.