COUNTIFS for Ongoing Training Tracker

I have built a large matrix across multiple sheets to track training data. In the chart, employee names are in the vertical and training activities in the horizontal. Each employee has either a date in a cell (when training occured) or a blank for that cell.

I've used conditional formatting to color in the cells when they are overdue (since the cell shows the training OCCURED, rather than the expiration), but now I want to create a chart for the managers to see (because all managers need charts).

I cannot figure out how to use COUNTIF or COUNTIFS to make it work. No matter how I try to word it, the formula returns no values.

Will you be able to upload your workbook...?

There is some private information on the workbook. I'll try to build a smaller version and upload it.

OK, after some playing around I found a solution. It isn't pretty, but it got it done for me.

- I have all of my dates on a separate sheet, call it MASTER.

- I built a key of the expiration dates for training under column T: =TODAY()-[x days]

- I used COUNTIF and COUNTIFS to find dates that fell within the ranges of the expiration dates listed in T

It looked like this:

=COUNTIFS(Master!U4:W50,”<=“&T5,Master!$U$4:$W$50,”>=“&T4)+COUNTIFS(Master!Y4:Z50,”<=“&T5,Master!$Y$4:$Z$50,”>=“&T4)+COUNTIFS(Master!AA4:AA50,”<=“&T5,Master!$AA$4:$AA$50,”>=“&T4)+COUNTIFS(Master!AF4:AF50,”<=“&T5,Master!$AF$4:$AF$50,”>=“&T4)+COUNTIFS(Master!AL4:AL50,”<=“&T5,Master!$AL$4:$AL$50,”>=“&T4)+COUNTIFS(Master!AG4:AI50,”<=“&T9,Master!$AG$4:$AI$50,”>=“&T8)+COUNTIFS(Master!AC4:AD50,”<=“&T13,Master!$AC$4:$AD$50,”>=“&T12)

The reason for the ugly breakdown is that I had trainings that were annual, biennial, every three years or once and done.

If there is a cleaner way of doing this, let me know.