"IF" Statements Help

[hintonpa]

Good Morning everyone,

I am trying to format the attached workbook so that the "Total Needing Certification" cells reflect the amount of "No" Statements in their related columns. Is there a way to do this that can be viewed in Excel 2003 and/or 2007?

Also, I wanted to be able to have the "Renewal Date" Columns to reflect different colors as follows:

More than 3 months from renewal date - Bright Green
Between 1 day and 3 months from renewal date - Yellow
Renewal Date and later - Red

I also plan to add several more employees to this list, so I need something that can be expanded. Anything I found on Google limits me to only 7 "IF" functions at once. Thanks in advance.

Phil

CPR and First Aid Training
Name	CPR	First Aid
XXXXXXXXXXXXXXXXXXXXXX	Current?	Renewal Date	Current?	Renewal Date
Employee 1	No	5/16/2011	Yes	5/16/2013
Employee 2	No	5/16/2011	Yes	5/16/2013
Employee 3	No	N/A	Yes	N/A
Employee 4	No	5/16/2011	No	5/16/2013
Employee 5	No	5/16/2011	No	5/16/2013
Employee 6	No	5/16/2011	No	5/16/2013
Employee 7	No	5/16/2011	Yes	5/16/2013
Employee 8	No	N/A	Yes	N/A
Employee 9	No	5/16/2011	Yes	5/16/2013
Employee 10	No	N/A	Yes	N/A
Employee 11	No	5/16/2011	Yes	5/16/2013
Employee 12	No	5/16/2011	Yes	5/16/2013
Total Needing Certification	12		3

<tbody>
</tbody>

 

[TheCman81]

To get the "Total Clarification" is easy, just use:

=COUNTIF(B4:B15,"No")

Obv change the range to suit

The other request can be done through conditonal formatting using a formula! I'll come back with the formula for this

 

[TheCman81]

Condition Format - Format using a formula

=TODAY()-C4>90 - For Green
=TODAY()-C4<90 - For Yellow
=C4>TODAY() - Red

Cell C4 represents the first renewal date in CPR, you should put the conditiona in the cell first, then manage rules and apply to range

Do the same for First Aid

 

[hintonpa]

That was fast...Thank you very much.