Excel Formula help please - using dates and COUNTIF

uberalles

Good morning

I would be grateful for any help please.

I have added a picture trying my best to explain what I would like a formula to calculate and here is the requirement.


" In cell F11 I would like, please, a formula that identifies the % of all staff in date with training.
It first acknowledges how many staff are in situ (Column A in light blue), this number will vary. Then looks at the dates in Column F (shaded grey), occasionally there will not be a date entered here, such as cell F7. These dates record the date this training was last taken and cell F3 (in orange) highlights the regularity that the training requires refreshing. If the date (in grey ) is less than 3 years old, then all is great. If however it is greater than 3 years old, or blank, then all is not good.
The formula needs to acknowledge all staff, calculate if dates given are within the threshold, then calculate the % in date with the training."

Any input greatly appreciated.

Hodge4ever

=(100/COUNTA(A4:A9))*COUNTIF(B4:B9,">"&TODAY()-1095)

There may be better ways but that should do the job, obviously you would have to change the formula for 2 years (i.e -730) and for your specific data ranges.

uberalles

Hodge4ever wrote: »

=(100/COUNTA(A4:A9))*COUNTIF(B4:B9,">"&TODAY()-1095)

There may be better ways but that should do the job, obviously you would have to change the formula for 2 years (i.e -730) and for your specific data ranges.

Thank you very much indeed Hodge4ever for your input, much appreciated.


In an hour's time I will put that into my sheet and let you know it has worked.

uberalles

Wonderful Hodge4ever, it works a treat. Much appreciated.