Percentage calculation based on multiple date criteria

Hello all,

I run into an issue when calculating percentage for this sheet. I have 70 rows of people, with 25 columns of dates. Each column is a particular training. Each row is a particular person.
Each training has an expiration date. Most yearly, 2 quarterly, and 2 'every 4 years'. I need to calculate the individual percentage of completion based on those dates.
Example:
Person 1 -- has 23 certifications that are up-to-date and in-line with the requirements per column. 1 certification was never completed (cell is blank), and 1 is expired (greater than that column's particular date).
His percentage should be calculated appropriately (92%).

How do I accomplish this?

Thanks!


Edit: Solved in post #8. See this post and last post for explanations and solutions.

Off the top of my head, the easiest (but definitely not prettiest or cleverest) would be to create a 2nd table based on the 1st

eg, assuming your 25 columns are from A - Y, with the required data in row1 and data from rows 2 onwards then in AA2 put

Please Login or Register  to view this content.

then drag this along and down to fill the table then in the cell you want your percentage put:

Please Login or Register  to view this content.

You can then hide the columns containing the 2nd table

It would probably help if you uploaded a sample workbook, showing what you have, and what you want?

Thanks for the file

So the dates in the table are the dates the test was taken, correct? And E4 would be duw for renewal on 31-May-2018 and F4 would be on 20-Nov-2015?

see the attached file one helper row created above the training period

No problem delete the Row number 4
check the report individual percentage formula also updated