Trying to measure compliance on expiry dates for training

Hi all,
I have a file I want to add some formulas to, that I "think" may be countif. The first formula is to return a compliance level in D5 by adding all the cells within a range with a date greater than today or NA - but anything less than today, or a blank cell it ignores.
The formulas are (I think) in

Please Login or Register  to view this content.

but can't quite finish it.
On the Compliance worksheet, this is where I really got lost!
I have a chart I want to populate based on the dates in the cells in the Current worksheet, but using a criteria of the conditional formatting within the date range of cells (3 rules), and return three values. 1 of anything within 30 days, 1 of anything within 90 days, and 1 of anything within 365 days.

Any help would be appreciated please.

May be:
=COUNTIF(G8:AW15,"<"&TODAY()+30)
???

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Are you positive that some of them dates are due in 3 months? I did a quick array formula & they're all showing up as false.

This was after converting the data to general and removing the text values in the data set with the array formula:

Please Login or Register  to view this content.

No, sorry. Your title needs to mention what you are trying to achieve. Try summarising the second and third lines of your opening post - that will tell us something about what you want to do. At the moment, it tells us nothing. Think Google search!

Much better - thank you.

Considering the statement: "The first formula is to return a compliance level in D5 by adding all the cells within a range with a date greater than today or NA" try:

Formula:

Please Login or Register  to view this content.

If that doesn't work it may help if you could tell us, based on the file attached to post #3, what value should be in cell D5 and also explain why.
Let us know if you have any questions.

This proposal adds a row below the table which may be moved and/or hidden for aesthetic purposes. Row 74 is populated using: =SUMPRODUCT((ISNUMBER(G8:G15))*(SUM(G8:G15,VLOOKUP(G5,dayslookup,2,0))>$A75))
Note that the formula isn't placed in columns O and Q as O5 and Q5 don't have values. The "3 years" are actually in N5 and P5 which are merged. My suggestion would be to unmerge the cells, place the "3 years" in cells O5 and Q5, then copy the formula from N74 across to Q74.
It appears that the merged cells are also causing a problem with cell O10 which incorrectly highlights green and Q10, Q15 which are not highlighted.
Note that A75 is populated using: =TODAY() so that each formula in G74:AW74 doesn't have to lookup today's date.
Cell BG5 is then populated using: =SUM(G74:AW74,COUNTIF(G8:AW15,"NA"))
Note that the denominator for cell D5 is changed to 328 as stated in post #14.
Let us know if you have any questions.

The thread is currently marked as solved ... You might wish to change it.

My apologies, the formula for the row below the table was not correct in my last post.
The following array entered formula* yields values that match the number of dates highlighted in green for each column:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
The following formula is used in cell T3: =SUM(G84:M84,COUNTIF(G8:M58,"NA"))
The following formula is used in cell D5: =T3/357
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.