Counting dates with Conditional Formatting

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.

Assuming no empty cells in either range...

=SUMPRODUCT((A1:B10TODAY())*(C1:C10"D"))

--
Biff
Microsoft Excel MVP


"NM" wrote in message
news

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.

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.

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:B10TODAY()),{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.

Hi,
Thanks much for your time and feedback. I tried the formula as you said but
its giving me #na error. I will need help to fix this and make the formula
work to get my results.
Thanks again for help.

"T. Valko" wrote:

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:B10TODAY()),{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.

If you want to send me a *small* sample file that shows what you have and
tell me what result you expect I can probably figure this out. If you want
to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,
Thanks much for your time and feedback. I tried the formula as you said
but
its giving me #na error. I will need help to fix this and make the formula
work to get my results.
Thanks again for help.

"T. Valko" wrote:

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:B10TODAY()),{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.

Hi,

Can you please send me your email address, so that I can send you a the
sample file.
My apologies but I could not comprehend the email provided below.

Thanks !

"T. Valko" wrote:

If you want to send me a *small* sample file that shows what you have and
tell me what result you expect I can probably figure this out. If you want
to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,
Thanks much for your time and feedback. I tried the formula as you said
but
its giving me #na error. I will need help to fix this and make the formula
work to get my results.
Thanks again for help.

"T. Valko" wrote:

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:B10TODAY()),{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.

Try this version:

xlcanhelpatcomcast.net

Remove all of the
Remove "can"

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,

Can you please send me your email address, so that I can send you a the
sample file.
My apologies but I could not comprehend the email provided below.

Thanks !

"T. Valko" wrote:

If you want to send me a *small* sample file that shows what you have and
tell me what result you expect I can probably figure this out. If you
want
to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,
Thanks much for your time and feedback. I tried the formula as you said
but
its giving me #na error. I will need help to fix this and make the
formula
work to get my results.
Thanks again for help.

"T. Valko" wrote:

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:B10TODAY()),{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.

I have sent you an email with the sample sheet. Can you please confirm
whether you have received it or not?

Thanks!

"T. Valko" wrote:

Try this version:

xlcanhelpatcomcast.net

Remove all of the
Remove "can"

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,

Can you please send me your email address, so that I can send you a the
sample file.
My apologies but I could not comprehend the email provided below.

Thanks !

"T. Valko" wrote:

If you want to send me a *small* sample file that shows what you have and
tell me what result you expect I can probably figure this out. If you
want
to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,
Thanks much for your time and feedback. I tried the formula as you said
but
its giving me #na error. I will need help to fix this and make the
formula
work to get my results.
Thanks again for help.

"T. Valko" wrote:

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:B10TODAY()),{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.

Yes, I have the file open in front on me.

Please verify that this is what you want to do:

You want to count "red dates" in columns J and K by row (not each cell) if
status in column L is not "D" ?


--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
I have sent you an email with the sample sheet. Can you please confirm
whether you have received it or not?

Thanks!

"T. Valko" wrote:

Try this version:

xlcanhelpatcomcast.net

Remove all of the
Remove "can"

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,

Can you please send me your email address, so that I can send you a the
sample file.
My apologies but I could not comprehend the email provided below.

Thanks !

"T. Valko" wrote:

If you want to send me a *small* sample file that shows what you have
and
tell me what result you expect I can probably figure this out. If you
want
to do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

--
Biff
Microsoft Excel MVP


"NM" wrote in message
...
Hi,
Thanks much for your time and feedback. I tried the formula as you
said
but
its giving me #na error. I will need help to fix this and make the
formula
work to get my results.
Thanks again for help.

"T. Valko" wrote:

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:B10TODAY()),{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.