Percentages off 2 text based fields

[reynastus]

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

[reynastus]

Not Sure if anyone is interested but i was playing round a bit today and talking to a few other people at work and we managed to come up with the solution

instead of using a COUNTIF statement as my Denominator I used a SUMPRODUCT statement to call both fields required.

Also changed around the spreadsheet a little bit and assigned numbers to the "Clinical" tag instead (1,2,3,4) making each number a certain level of requirements. This means that i can count out people as well (ie 2x people in administration, 1 of them a level 1 and the other a level 4, the level 4 has to do 2 courses where as the level one only has to do 1, I can now get a percentage of the people in the unit that need to do the course)

If anyone is interested the formula I am using now is

=SUMPRODUCT(--(Yearlys!E3:E2004>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*(Yearlys!C3:C2004="Administration")*(Yearlys!D3:D2004>=1)*1)/SUMPRODUCT((Yearlys!C3:C2004="Administration")*(Yearlys!D3:D2004>=1))

the (Yearlys!D3:D2004>=1) part is the number part to change for the different levels (>=1 would mean everyone over the number 1 would need to do that course, so (Yearlys!D3:D2004=4) would mean only people of level 4 would need to do the course

hope this helps someone and or what ever

Rey