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!

On Nov 16, 12:46 pm, Christine <Christ...@discussions.microsoft.com>
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

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 <Christ...@discussions.microsoft.com>
> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:90267C3A-2A7B-4FB5-82B0-(E-Mail Removed)...
> 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 <Christ...@discussions.microsoft.com>
> > 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
> >
> >

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" <(E-Mail Removed)> wrote in message
> news:90267C3A-2A7B-4FB5-82B0-(E-Mail Removed)...
> > 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 <Christ...@discussions.microsoft.com>
> > > 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
> > >
> > >
>
>
>

Hi again. I'm having difficulty getting the code Peter gave me so nicely and
hope he or Mike or someone can help again.

In F23 through F27 I have conditional formatting set as follows. This is to
show me any tasks that are overdue, due within the next week, and those that
are due in over 2 weeks.

* Cell value is less than =Today() turn font red, bold
* Cell value is less than =Today()+7 turn font yellow, bold
* Cell value is less than =Today()+14 turn font green

NOTE: Technically, I don't want the fonts colored at all if any cells
between Q23 and Q27 have dates in them! This is because Q23 and Q27 have
completion dates in them.

Anyway, in F23 through F27 the cell values are thus:
11-Nov-05 (formatted bold red)
08-Nov-02 (formatted bold red)
18-Nov-06 (formatted bold yellow)
19-Nov06 (formatted bold yellow)
30-Nov-06 (formatted bold green)

However, when I use the following commands for counting the occurances, it
reseults in the following:

=COUNTIF(F23:F27,"<"&(TODAY())) - results in 2 (okay - should be 2)
=COUNTIF(F23:F27,"<"&(TODAY()+7)) - results in 4 (should be 2)
=COUNTIF(F23:F27,"<"&(TODAY()+14)) - results in 5 (should be 1)

Hello again,

Assuming Today() is date of post (17-Nov-06) your formulas are returning
correct results.

If you apply what are currently your 2nd or 3rd CF's as the 1st I think
you'll see the confusion.

Try these
=SUMPRODUCT(--(F23:F27<TODAY()))
=SUMPRODUCT(--((F23:F27<(TODAY()+7))*(F23:F27>=TODAY())))
=SUMPRODUCT(--((F23:F27<(TODAY()+14))*(F23:F27>=TODAY()+7)))

Alternatively these entered in A1:A3
=SUMPRODUCT(--(F23:F27<TODAY()))
=SUMPRODUCT(--(F23:F27<(TODAY()+7)))-A1
=SUMPRODUCT(--(F24:F28<(TODAY()+14)))-A1-A2

No doubt can be adapted to COUNTIF if you prefer.

Regards,
Peter T

"Christine" <(E-Mail Removed)> wrote in message
news:B6904BEC-CB9A-4453-9CF0-(E-Mail Removed)...
> Hi again. I'm having difficulty getting the code Peter gave me so nicely
and
> hope he or Mike or someone can help again.
>
> In F23 through F27 I have conditional formatting set as follows. This is
to
> show me any tasks that are overdue, due within the next week, and those
that
> are due in over 2 weeks.
>
> * Cell value is less than =Today() turn font red, bold
> * Cell value is less than =Today()+7 turn font yellow, bold
> * Cell value is less than =Today()+14 turn font green
>
> NOTE: Technically, I don't want the fonts colored at all if any cells
> between Q23 and Q27 have dates in them! This is because Q23 and Q27 have
> completion dates in them.
>
> Anyway, in F23 through F27 the cell values are thus:
> 11-Nov-05 (formatted bold red)
> 08-Nov-02 (formatted bold red)
> 18-Nov-06 (formatted bold yellow)
> 19-Nov06 (formatted bold yellow)
> 30-Nov-06 (formatted bold green)
>
> However, when I use the following commands for counting the occurances, it
> reseults in the following:
>
> =COUNTIF(F23:F27,"<"&(TODAY())) - results in 2 (okay - should be 2)
> =COUNTIF(F23:F27,"<"&(TODAY()+7)) - results in 4 (should be 2)
> =COUNTIF(F23:F27,"<"&(TODAY()+14)) - results in 5 (should be 1)
>
>
>

I see said the blind man.....er..women! Thanks so very much for your help,
Peter. Seeing your code and putting it into action has helped me understand
what was happending. Your a star!

Cheers,

"Peter T" wrote:

> Hello again,
>
> Assuming Today() is date of post (17-Nov-06) your formulas are returning
> correct results.
>
> If you apply what are currently your 2nd or 3rd CF's as the 1st I think
> you'll see the confusion.
>
> Try these
> =SUMPRODUCT(--(F23:F27<TODAY()))
> =SUMPRODUCT(--((F23:F27<(TODAY()+7))*(F23:F27>=TODAY())))
> =SUMPRODUCT(--((F23:F27<(TODAY()+14))*(F23:F27>=TODAY()+7)))
>
> Alternatively these entered in A1:A3
> =SUMPRODUCT(--(F23:F27<TODAY()))
> =SUMPRODUCT(--(F23:F27<(TODAY()+7)))-A1
> =SUMPRODUCT(--(F24:F28<(TODAY()+14)))-A1-A2
>
> No doubt can be adapted to COUNTIF if you prefer.
>
> Regards,
> Peter T
>
> "Christine" <(E-Mail Removed)> wrote in message
> news:B6904BEC-CB9A-4453-9CF0-(E-Mail Removed)...
> > Hi again. I'm having difficulty getting the code Peter gave me so nicely
> and
> > hope he or Mike or someone can help again.
> >
> > In F23 through F27 I have conditional formatting set as follows. This is
> to
> > show me any tasks that are overdue, due within the next week, and those
> that
> > are due in over 2 weeks.
> >
> > * Cell value is less than =Today() turn font red, bold
> > * Cell value is less than =Today()+7 turn font yellow, bold
> > * Cell value is less than =Today()+14 turn font green
> >
> > NOTE: Technically, I don't want the fonts colored at all if any cells
> > between Q23 and Q27 have dates in them! This is because Q23 and Q27 have
> > completion dates in them.
> >
> > Anyway, in F23 through F27 the cell values are thus:
> > 11-Nov-05 (formatted bold red)
> > 08-Nov-02 (formatted bold red)
> > 18-Nov-06 (formatted bold yellow)
> > 19-Nov06 (formatted bold yellow)
> > 30-Nov-06 (formatted bold green)
> >
> > However, when I use the following commands for counting the occurances, it
> > reseults in the following:
> >
> > =COUNTIF(F23:F27,"<"&(TODAY())) - results in 2 (okay - should be 2)
> > =COUNTIF(F23:F27,"<"&(TODAY()+7)) - results in 4 (should be 2)
> > =COUNTIF(F23:F27,"<"&(TODAY()+14)) - results in 5 (should be 1)
> >
> >
> >
>
>
>