| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
Assuming no empty cells in either range...
=SUMPRODUCT((A1:B10<TODAY())*(C1:C10<"D")) -- Biff Microsoft Excel MVP "NM" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
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: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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
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: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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
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: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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
Try this version:
xl<canhelp<atcomcast<.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: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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
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: xl<canhelp<atcomcast<.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: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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
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: xl<canhelp<atcomcast<.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: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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
Ok...
I'm only looking at columns J & K... Your conditional formatting is based on column L not being "D" so you have to consider that to count the "red cells". I'm not actually counting "red cells". I'm using the logic of *why* the cells are red to arrive at the count. So, for columns J & K use this formula: =SUMPRODUCT(--(((J13:J120<"")*(J13:J120<=C9))+((K13:K120<"")*( K13:K120<=C9))0),--(L13:L120<"D")) The correct result is 35. You have an incorrect date entry in cell K13: 10/1008 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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: xl<canhelp<atcomcast<.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: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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
You are a genius!!!Thank you so much for your solution it works great!! I
have been looking for an answer for this problem in so many websites, but didn't get an answer! I am grateful to you for your help !! Thanks tons!! "T. Valko" wrote: Ok... I'm only looking at columns J & K... Your conditional formatting is based on column L not being "D" so you have to consider that to count the "red cells". I'm not actually counting "red cells". I'm using the logic of *why* the cells are red to arrive at the count. So, for columns J & K use this formula: =SUMPRODUCT(--(((J13:J120<"")*(J13:J120<=C9))+((K13:K120<"")*( K13:K120<=C9))0),--(L13:L120<"D")) The correct result is 35. You have an incorrect date entry in cell K13: 10/1008 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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: xl<canhelp<atcomcast<.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: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. |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "NM" wrote in message ... You are a genius!!!Thank you so much for your solution it works great!! I have been looking for an answer for this problem in so many websites, but didn't get an answer! I am grateful to you for your help !! Thanks tons!! "T. Valko" wrote: Ok... I'm only looking at columns J & K... Your conditional formatting is based on column L not being "D" so you have to consider that to count the "red cells". I'm not actually counting "red cells". I'm using the logic of *why* the cells are red to arrive at the count. So, for columns J & K use this formula: =SUMPRODUCT(--(((J13:J120<"")*(J13:J120<=C9))+((K13:K120<"")*( K13:K120<=C9))0),--(L13:L120<"D")) The correct result is 35. You have an incorrect date entry in cell K13: 10/1008 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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: xl<canhelp<atcomcast<.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: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. |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting dates with Conditional Formatting
Moving in the same lines. I have another question for you. In the same sheet,
how can I count the number of rows which have red dates? So, a row might have 1 or 4 or 6 red dates but we will just count it as one. In a nutshell, i want to count the total number of rows having red dates, no matter how many they have. Thanks again for your help. "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "NM" wrote in message ... You are a genius!!!Thank you so much for your solution it works great!! I have been looking for an answer for this problem in so many websites, but didn't get an answer! I am grateful to you for your help !! Thanks tons!! "T. Valko" wrote: Ok... I'm only looking at columns J & K... Your conditional formatting is based on column L not being "D" so you have to consider that to count the "red cells". I'm not actually counting "red cells". I'm using the logic of *why* the cells are red to arrive at the count. So, for columns J & K use this formula: =SUMPRODUCT(--(((J13:J120<"")*(J13:J120<=C9))+((K13:K120<"")*( K13:K120<=C9))0),--(L13:L120<"D")) The correct result is 35. You have an incorrect date entry in cell K13: 10/1008 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... 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: xl<canhelp<atcomcast<.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: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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting Conditional Formatting Results | Excel Discussion (Misc queries) | |||
| Conditional formatting and counting. | Excel Discussion (Misc queries) | |||
| Conditional formatting and then counting specifics within it. | New Users to Excel | |||
| Counting Cells with Conditional Formatting | Excel Discussion (Misc queries) | |||
| counting cells with conditional formatting applied | Excel Discussion (Misc queries) | |||
Linear Mode
