Percentages off 2 text based fields

Hi Guys

So ive got this wonderful spread sheet that has been developed through the help of this community.

so far ive gotten everything to work but managed to find an area that requires a bit more of a specific thing

so far i have

Name | Unit | Clinical | Course 1 | Course 2 | Course 3 |
-------------------------------------------------------------------------------------------

all that are generating reports as they should (based on the date and unit to create a rolling percentage) using the following formula

=SUMPRODUCT(--(Yearlys!E3:E2004>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))),(Yearlys!B3:B2004="Administration")*1)/COUNTIF(Yearlys!B3:B2004,"Administration")

my question is, is there a way to add another field in so that i can work out a percentage of people that meet both criteria (eg 2 people that work in the same area, however one person is required to do a course but the other isnt) I'm thinking i need to use an AND function in the

 
(Yearlys!B3:B2004="Administration")*1)/COUNTIF(Yearlys!B3:B2004,"Administration")

but i cant make it work

also if someone is away for a long time is there a way (by putting another field in at the end) that would be able to flag those people as not being relevant (so far im thinking of just cutting and pasting them to another worksheet)

Attached is the spreadsheet as it stands.

Thanks for the help

Rey