Cond. Form. Adding In dates & out of dates
 

I have part of a training programme which I've copied below. Column's F & G
is another Course, I & J another and so on.
Currently, I have C1 that adds up all the Yess =COUNTIF(C8:C36,"Y")
And C2 that adds up all the Nos =COUNTIF(C8:C36,"N")

In cell D8, it is conditional formatted using Formula Is
=AND(D8<€�€�,DATE(YEAR(D8)+3,MONTH(D8),DAY(D8)) <=TODAY()) €¦..which is
formatted pink
This means that in 3 years time from the date thats entered into D8, it
will turn pink which indicates to me that the persons training is now out of
date. D9, D10 follow as above.

There are another two conditions which indicate a different colour 2 yrs 9
months orange and 2 yrs 6 months indicating yellow, both used as a warning
that training will be required soon

Anyway, I would like to get rid of the C Column and instead have Cells D8 to
D36 to automatically add up and indicate the total €˜Out of dates in cell D2
and the total €˜in dates in cell D1

A B C D
1 . . total Yes-indate
2 . . total No-out of date
3 . . . .
4 . . €¦€¦.€¦First Aid€¦€¦€¦.. .
5 . . €¦€¦.€¦.Course€¦€¦€¦€¦. .
6 . . €¦€¦...€¦3 year€¦.€¦€¦€¦ .
7 . . Y/N Date

8 . Name1 ? ?

9 . Name2 ? ?

10 . Name3 ? ?

Can this be done using excel?