Hoping someone is an excel guru have. Searched tinternet but can't find a solution

I have a spread sheet that has dates a test was taken and they are conditionally formatted to go green if the date is between today() and today()-600

Basically anyone with a date approaching within the 600 days is green then after this it is formatted to change Amber so we know they need to retake a test etc.

How can I get a seperate cell to count how many cells have a date that is between today() and today()-600 using the conditional formatting rims as I know excel does not count colours well.

Something like COUNTIF(A1:A20, =Today() and today()-600)

Thanks in advance.

You can use an array formula to do it (if you google array formula you can find quite a few explanations of how they work)

 

In this case type:

 

=sum((A1:A20<=today())*(A1:A20>=(today()-600))*1)

 

then press ctrl-shift-enter to make it an array formula and then it will surround it with { }

 

Hope that helps

Many Thanks...worked a treat

Excel formula problems are strangely compelling when you get stuck into fixing them.

Is it just me who thinks that the 2010 release of excel is way more limited in how much you can do with it, a lot harder to set up complex equations like the one here?

Is it just me who thinks that the 2010 release of excel is way more limited in how much you can do with it, a lot harder to set up complex equations like the one here?

 

Not sure, I've used it for so long now.

 

I suppose I do find myself resorting to VBA more than I think I used to.

I hate excel :(