counting based on conditional format

hi hope theres a way to do this.

i have a spreadsheet which is used to keep the training records for the company it counts no of staff and their skill levels for different parts of the job, when a person moves to a different branch the skill required changes and they have a set period of time to aquire the new skill.

when the time is up the colour of the old skill is changed to a red font by a conditional format based on the date.this is then counted by a calculation so the team know how many people are to be targeted for extra training.

the problem i have is the calculation will not count conditionally formated cells, so whenever i update the sheet i have to go to each cell with red text and change the font colour manually to red, this sounds really silly and is impossible to explain to anyone who does not understand conditional formatting. i e the people who will end up using the database

what im looking for is a way to count conditionally formatted cells based on the condition being met i.e. they are showing as red font.

can it be done and how?
or am i wasting my time looking for an answer to something excel wont do ?

already been there, if you read through about 3/4 way down the page there are a couple of constraints, the one that concerns me i've copied below

"The second shortcoming is that this technique at present does not cater for cells that are coloured due to conditional formatting"

the result being that this will do what i already have in use but not what i'm trying to achieve.

thanks for the link anyway, couple of things in there that may be useful in the future

this looks like what i need but i cant get it to work, i've copied the code in and set up a formula to call it but all i get is #value!. the formula im using is =sumbycfcolorindex(h11:h91,1). i've tried changing the last value as advised on the article but just get the same result. what am i doing wrong(PROBABLY SOMETHING SIMPLE)

I think I am just expounding on oldchippy's idea. Why not just count BASED on the condition. For example, if the cell's font changes to red if the current date is 30 days after the date in the cell, your conditional format may look something like:

=A2>TODAY()+30 (or something like that)

In that case, just set up a COUNT IF type array:

=SUMPRODUCT(--(A2>TODAY()+30))

dont know if these would work but attached a cut down version of the workbook so you can see what im trying to do hope you can suggest something

I agree with BigBas

You seem to be setting the colour of a cell based on a condition (which gives a logical value) then trying to count the coloured cells.

Why can't you count the number of cells that match the same condition that you use in the conditional format using COUNTIF() (or similar)?

for example:

=countif(A2:D256,">"&E22)

where A2:D256 is the array of dates to be checked, E22 is the date beyond which the condition changes.

that appears to assume the date is always the same. this is not the case, the date changes for each entry and the calculation cant go in the target cells as they already contain needed information

tried this and got the result you said, 3 but on all 4 columns the same result whereas the 4 columns H,I,J,K all need to total individually so the actual result i would need on the sample posted would be H=0, I=3, J=0, K=0

thanks for all your help but happy to report that i've solved the problem by coming at it backwards, i re wrote the conditional format to infill the cells in green then just instructed the user to change the text colour of any green cells to red, the thing that was confusing them was having to change the text to red when(as far as they could see) it was already red

thanks again