"IF" Statements Help

hintonpa

New Member
Joined
Jun 29, 2012
Messages
2
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
NameCPRFirst Aid
XXXXXXXXXXXXXXXXXXXXXXCurrent?Renewal DateCurrent?Renewal Date
Employee 1No5/16/2011Yes5/16/2013
Employee 2No5/16/2011Yes5/16/2013
Employee 3NoN/AYesN/A
Employee 4No5/16/2011No5/16/2013
Employee 5No5/16/2011No5/16/2013
Employee 6No5/16/2011No5/16/2013
Employee 7No5/16/2011Yes5/16/2013
Employee 8NoN/AYesN/A
Employee 9No5/16/2011Yes5/16/2013
Employee 10NoN/AYesN/A
Employee 11No5/16/2011Yes5/16/2013
Employee 12No5/16/2011Yes5/16/2013
Total Needing Certification123

<tbody>
</tbody>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
To get the "Total Clarification" is easy, just use:

Code:
=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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,211,983
Messages
6,105,208
Members
447,955
Latest member
BWheezy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top