Counting dates with Conditional Formatting

Hi,

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 M,D or N(M=Match, D=DOne,N=Not Applicable).If the Status field is D the date is no more red even
if it is less than today( I set conditional formatting).The status for these dates in not D.When there is no date in column a & b the status in column c is 'N'. Now,I want to count the number of red dates in cloumn a or b. And if there is a red date in both a1 and b1, I just want to count it as 1 and not 2.

Please let me know the exact formula to be used.

Thanks in advance.

NM

try this if your cells will not turn red when they are empty

=SUMPRODUCT(SIGN((A12:A14>TODAY())+(B12:B14>TODAY())),--(C12:C14="D"))

Originally Posted by sglife

try this if your cells will not turn red when they are empty

=SUMPRODUCT(SIGN((A12:A14>TODAY())+(B12:B14>TODAY())),--(C12:C14="D"))

Hi there,

have a colums like A=Name
B=date when people borrwod money
C=amount
D=last date to repay
if the date in coloum "D" is greater then today's date the "A" shold appear in RED colour can we fix this any way...

can you attach the file?