Excel guru help

[ClemUK]

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.

[jz19]

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

[ClemUK]

Many Thanks...worked a treat

[Aspman]

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

[mac11irl]

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?

[Aspman]

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.

[Dean-]

I hate excel :(