COUNTIF Dates expired. HELP!

[jimbo100]

Hey guys, I've been trawling the internet, and it's many excel forums, and am yet to find exactly what I'm looking for!

I've been working on a training matrix/schedule and my boss has asked me to come up with a % compliance figure. I really need some help understanding exactly what 'COUNTIF' formula I need to be using, given my criteria;

- Not all people need to be trained, but are present on the matrix - if they do not need training, then an 'x' is in their row instead of date
- Training courses last for 3 years and the date specified is the date they were trained.
- Dates within 3 years are green
-Dates that will expire (+ 3 years or 1095 days) in 90 days are yellow
-expired dates are red.

http://i67.tinypic.com/m9oxz8.png

So basically, I want two formulas - 1 for the 'Targeted No.' and the other for 'Compliance %'

I hope my image has attached of my example spreadsheet, otherwise, this won't make any sense! If it hasn't, I'll amend the post.

Would greatly appreciate any help on this!

Thanks!

 

[Kerryx]

Targeted No = =CONCAT((COUNT(C3:C12))&" of "&COUNTA(C3:C12))

 

[jimbo100]

Targeted No = =CONCAT((COUNT(C3:C12))&" of "&COUNTA(C3:C12))

That worked perfectly! Thanks!

Now just the 'Compliance %' left to solve

 

[steve the fish]

Try:

=COUNTIF(C3:C12,">"&EDATE(TODAY(),-36))/COUNT(C3:C12)

 

[Kerryx]

that worked perfectly! Thanks!

Now just the 'compliance %' left to solve

=sum(countif(c3:c12,"<"&today()-1095)/count(c3:c12))*100

 

[jimbo100]

=sum(countif(c3:c12,"<"&today()-1095)/count(c3:c12))*100

You're an absolute star! Thank you so much!!