Conditional Formatting - formulas

[exhibition]

Operating System: Mac OS X 10.5 (Leopard)

At the moment, I am using the following conditional formatting formula to turn a cell red if the due date for a task has been passed:

=AND(F27>0,F27<TODAY())

I would like to have another cell that totals how many items have passed their due dates (if the cell has turned red, I would like to know how many items we have overdue). I want to use the COUNTIF formula... how do I count when a cell colour has filled into red? Is there another way to do this? Thanks.

=COUNTIF($C$1:$C$65496,"conditional formatting = red cell")

 

[katie_c]

Why not try an array formula instead of countif? Basically, the following formula says that if the value is greater than 0 and less than Today, it will treat it as a 1 and sum it up. Array formulas basically set 0/1 values based on conditions.

To get the squiggly brackets, instead of hitting enter, use control shift enter.

{=SUM(($C$1:$C$65496>0)*($C$1:$C$65496<TODAY()))}

-Katie

 

[JE McGimpsey]

Operating System: Mac OS X 10.5 (Leopard)

At the moment, I am using the following conditional formatting formula to
turn a cell red if the due date for a task has been passed:

=AND(F27>0,F27<TODAY())

I would like to have another cell that totals how many items have passed
their due dates (if the cell has turned red, I would like to know how many
items we have overdue). I want to use the COUNTIF formula... how do I count
when a cell colour has filled into red? Is there another way to do this?
Thanks.

=COUNTIF($C$1:$C$65496,"conditional formatting = red cell")

One way (if using XL08):

=COUNTIFS(C:C,">0",C:C,"<"&TODAY())

Another (pre-XL08):

=SUMPRODUCT(--(C1:C60000>0),--(C1:C60000<TODAY()))