Count and Sum with Conditional Formatting Problem
 

Hello:

I have the following conditional formatting conundrum related to an
excel based gantt chart:

I have a range of cells with no values in them. Instead I have them set
to turn green in color based on conditional formatting.

The conditions are as follows.

FormulaIs = D$1=$B2
FormulaIs = D$1=$C2
FormulaIs = AND(D$1$B2, D$1<$C2)

When true - cells turn green indicating the condition is met. If not,
they are white or transparent.

I would like to sum or count the cells that turn green when these
conditions are met. The green cells represent time bars that match
start and end times of an event in a given row. Summing would occur
across a range of cells. I would like to mention that I have not added
any values to these cells.

I have tried to use code from Chip Pearson's site, but to no avail. I
really need to sum up or count the cells that meet my conditional
formatting requirements. Please help. Your help is much appreciated.
Thanks,

PM

Hi!

You need to "place" something in the cell that can be
evaluated and used in a sum/count type formula.

For instance, write an IF formula that uses a value if
true argument of <space:

=IF(D1=B2," ","")

You don't see the <space but now, that <space can be
counted.

=SUMPRODUCT(--(LEN(A1:A10)=1))

Biff

-----Original Message-----
Hello:

I have the following conditional formatting conundrum
related to an
excel based gantt chart:

I have a range of cells with no values in them. Instead I
have them set
to turn green in color based on conditional formatting.

The conditions are as follows.

FormulaIs = D$1=$B2
FormulaIs = D$1=$C2
FormulaIs = AND(D$1$B2, D$1<$C2)

When true - cells turn green indicating the condition is
met. If not,
they are white or transparent.

I would like to sum or count the cells that turn green
when these
conditions are met. The green cells represent time bars
that match
start and end times of an event in a given row. Summing
would occur
across a range of cells. I would like to mention that I
have not added
any values to these cells.

I have tried to use code from Chip Pearson's site, but to
no avail. I
really need to sum up or count the cells that meet my
conditional
formatting requirements. Please help. Your help is much
appreciated.
Thanks,

PM

.