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 Yes’s =COUNTIF(C8:C36,"Y")
And C2 that adds up all the No’s =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 that’s 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 date’s’ 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?