Thank you Peter - that's what I wanted! Now to figure out how to do the rest!

Cheers,


"Peter T" wrote:

Hi Help,

Try this one

=SUMPRODUCT(--(F23:F27<TODAY()))

but if you particularly want to stick with countif
=COUNTIF(F22:F26,"<"&VALUE(TODAY()))

Regards,
Peter T

"Christine" wrote in message
...
Hi Mike. I'm afraid that didn't work. The only thing I changed was to put
in
some of the actual cell references in my worksheet...
=SUM(IF(F22:F26-TODAY()<7,1,0))

Also, since I don't actually want to sum the date cells - only get the
number of cells that meet the criteria, I tried the following:

=COUNTIF(F22:F26,"<TODAY()")

For the sample data below, the result came out as 0 where it should have
been 2

Col F
Row 22 11-Nov-02
23 11-Nov-03
24 16-Nov-06

Help!

"Mike Woodhouse" wrote:



On Nov 16, 12:46 pm, Christine
wrote:
After reading quite a few posts I see there is no way to count the
number of
color entries in a worksheet - and have the numbers update "live" and
automatically as coloring changes. This is a problem for my
requirements, in
addition to some other functionality that my worksheet requires.
Hopefully
someone can take this on!!

What I'm trying to do is to highlight overdue tasks, tasks that are
due
within the next seven (preferably working) days, tasks due in 2 weeks
time,
and show a summary of these (and thus progress against the project) in
a
Red/Green/Yellow table.

The worksheet has a number of cells where the user will enter a dates
for
when a task is due and when the task is complete. These date cells
have the
"CalcProgress" style applied to them. I want to highlight the due
dates in
green and red, and automatically total the number of these coloured
entries
in the worksheet and update the totals as they change.

For example:

Total Red Cells: nnn
Total Green Cells: nnn
Total Yellow Cells: nnn

If the due date is <=Today() , the font should be red, bold

If the due date is < =TODAY()+7, the font should be yellow bold

If the due date is < =TODAY()+14, the font should be green (black, not
bold)

HOWEVER!! When the user enters a date in the "Date Complete" cell for
the
item, due date colouring should revert to plain text (normal). As the
"Date
Complete" cells are filled, the total count of red, green and yellow
cells
would decrease accordingly.

Hope this is understandable - and possible!!

Any assistance will be really, really appreciated!

For the automatic colouring, you're probably going to need Conditional
Formatting (on the Format menu). I don't know if this can be attached
to a Style or not... I suggest you use the "Formula Is" option rather
than "Cell Value Is".

While it's fairly easy normally to establish the colour (background or
font) of a cell via a smallish VBA function, it's tricker by far when
the colouring is applied via conditional format. Ticky enough that I
recommend you avoid it, if only because I don't know right now how best
to go about it. (It's likely to involve examining the formatting
conditions in code, working out what they think about things and what
the result would be - as I say, tricky).

Fortunately, we don't have to count cells by colour, we can just count
the numbers that meet our formatting conditions. There's COUNTIF(),
which I don't like much (can be slow and I hate putting formula-type
stuff in quoted text) and then there are array formulae, which I
perhaps like too much...

Say your due dates are in cells B2:B99, then to count the dates that
are within 7 days from now, enter the following:

=SUM(IF(B2:B99-TODAY()<7,1,0))

....and enter it into the spreadsheet using Control+Shift+Enter (hold
down Control and Shift before pressing Enter) which creates an array
formula. The yellow and green counts are left as an exercise for the
student... ;)

HTH,

Mike