if there is a red date in both a1 and b1, I just
want to count it as 1 and not 2.

Ok, in other words you want to count the number of ROWS that meet the
condition, not every cell?

Try this:

=SUMPRODUCT(--(MMULT(--(A1:B10<"")*(A1:B10<TODAY()),{1;1})0),--(C1:C10<"D"))

This will only work if the range is less than 5462 rows.

If that doesn't work then you'll have to use some VBA code and it's more
complicated since you're using conditional formatting. See this:

http://www.cpearson.com/Excel/CFColors.htm


--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi T.Valko,

Thanks for your feedback. But there is still something I need to fix. I do
have Blank cells in the Columns a &b. And if there is a red date in both
a1
and b1, I just want to count it as 1 and not 2.
I will appreciate your help.
Thanks!


"NM" wrote:


I have 3 columns a,b,c.Column a,b contains dates.The dates in these
columns
turn red if it is less than today.Column c is Status field which contains
D
or M(M=Match, D=DOne).If the Status field is D the date is no more red
even
if it is less than today( I set conditional formatting).Now,I want to
count
the number of red dates in cloumn a or b .The status for these dates in
not D.