problem using conditional formatting to flag cells by color and count them

Select column E and change Conditional format setting to the attached.

Perhaps using a helper column, so you can count the colours after that. You can then hide this helper column - see attached.

Otherwise, you will have to settle to using VBA for the counting part.

That is because your formulas are offset by one cell.

Select all of column E, by click the E at the top of the column, then invoke Conditional formatting and change all the E2 and F2 references to E1 and F1, respectively.

Not sure what you are asking.. did the conditional formatting (without the helper column) that I offered not work?

With the helper column that ron offered, you are entering the formula in G2 and copying it down the column, then, yes, conditional formatting is applied still to column E and is simply checking what number is in the helper to determine which colour to use.

See attached.

In each sheet, in I2 to count Yellow:

=SUMPRODUCT(--($E$2:$E$9<>""),--($F$2:$F$9=""),--(TODAY()>=$E$2:$E$9+10),----(TODAY()<=$E$2:$E$9+20))

in I3 to count Orange:

=SUMPRODUCT(--($E$2:$E$9<>""),--($F$2:$F$9=""),--(TODAY()>=$E$2:$E$9+20),--(TODAY()<=$E$2:$E$9+30))

and in I4 to count Red:

=SUMPRODUCT(--($E$2:$E$9<>""),--($F$2:$F$9=""),--(TODAY()>=$E$2:$E$9+30))

adjust the ranges to suit.. No whole column references.

Then in summary sheet, list the sheet names in A2 down and in B2, for Yellow:

=INDIRECT("'"&$A2&"'!I2")

copied down

in C2, For Orange:

=INDIRECT("'"&$A2&"'!I3")

copied down

in D2, For Red:

=INDIRECT("'"&$A2&"'!I4")

copied down

Try this formula in I5 of each sheet:

=SUMPRODUCT(--(E2:E200<>""),--(F2:F200=""))-SUMPRODUCT(--($E$2:$E$200<>""),--($F$2:$F$200=""),--(TODAY()>=$E$2:$E$200+30))

which is essentially the sum of all IN DATES with no COM DATES minus the Count of RED as in I4 of each sheet.

The #VALUE error in the Narula sheet was caused by a typo you had in cell E13. You have date entered as 106/11 instead of 10/6/11

Sorry, where are we looking?

See attached.

Conditional formatting formula for column F:

=AND(E1<>"",F1<>"",F1>E1+30)

Formula in I4 adjusted to:

=SUMPRODUCT(--($E$2:$E$200<>""),--($F$2:$F$200=""),--(TODAY()>=$E$2:$E$200+30))+SUMPRODUCT(--($E$2:$E$200<>""),--($F$2:$F$200<>""),--($F$2:$F$200>$E$2:$E$200+30))

Formula in I5 adjusted to:

=SUMPRODUCT(--(E2:E200<>""),--(F2:F200=""))-SUMPRODUCT(--($E$2:$E$200<>""),--($F$2:$F$200=""),--(TODAY()>=$E$2:$E$200+30))+SUMPRODUCT(--($E$2:$E$200<>""),--($F$2:$F$200<>""),--($F$2:$F$200<=$E$2:$E$200+30))