Countif date is greater than today's date

[adamkim]

Hello - I need to count how many courses are past their estimated release date, starting with today's date.

For example,

<TABLE style="WIDTH: 361pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=481 border=0 x:str><COLGROUP><COL style="WIDTH: 213pt; mso-width-source: userset; mso-width-alt: 10386" width=284><COL style="WIDTH: 148pt; mso-width-source: userset; mso-width-alt: 7204" width=197><TBODY><TR style="HEIGHT: 26.25pt" height=35><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 213pt; BORDER-BOTTOM: windowtext 1.5pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: transparent" width=284 height=35>Course Title</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 148pt; BORDER-BOTTOM: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent" width=197>Estimated Release for Sale Date</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 213pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=284 height=18>ARELLO Truth About Mold w/exam v2.0</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BORDER-LEFT: windowtext; COLOR: red; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=197 x:num="39995">7/1/09</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 213pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=284 height=34>California - Licensing Packages and On Demand Supplemental Course</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-LEFT: windowtext 0.5pt solid; COLOR: red; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; mso-ignore: style" width=197 x:num="40122">11/5/09</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 213pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=284 height=34>Environmental Issues in Your Real Estate Practice (Texas Version)</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 148pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=197 x:num="40144">11/27/09</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>How many courses are past today's date:</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="'=COUNTIF(B2:B4,"<TODAY()")">=COUNTIF(B2:B4,"<TODAY()")</TD></TR></TBODY></TABLE>

 

[khamilton]

try this: =SUMPRODUCT(--(B2:B4>TODAY()))

 

[adamkim]

Still getting the wrong count. It is yielding a count of 40 but it should be a total of 26. My guess is that it is counting the blank cells, because there are courses without product release dates specified.

<TABLE style="WIDTH: 364pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=484 border=0 x:str><COLGROUP><COL style="WIDTH: 212pt; mso-width-source: userset; mso-width-alt: 10313" width=282><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl97 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 264pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #99ccff" width=351 colSpan=2 height=21>Course Information</TD><TD class=xl100 id=td_post_2132506 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 100pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=133>Product Release</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 212pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" width=282 height=17>WA Advanced Real Estate Practices</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=69>Need PPN</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 100pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=133 x:num="40299">5/1/10</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 212pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #99ccff" width=282 height=21>WA ETHICS IN TODAY'S REAL ESTATE WORLD</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=69>3017981W</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-LEFT: windowtext 0.5pt solid; COLOR: red; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; mso-ignore: style" width=133 x:num="40118">11/1/09</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 212pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: #99ccff" width=282 height=30 x:str="WA Foreclosures, Short Sales, REO's & Auctions without Exam ">WA Foreclosures, Short Sales, REO's & Auctions without Exam </TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=69>3017961W</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; FONT-WEIGHT: 700; BORDER-LEFT: windowtext 0.5pt solid; COLOR: red; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; mso-ignore: style" width=133 x:num="40118">11/1/09</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl85 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 212pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" width=282 height=17>WA RE Broker Pre-Licensing 30HR: Practices</TD><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=69 x:num>15173321</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 100pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=133 x:num="40299">5/1/10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 212pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99ccff" width=282 height=17>WA Real Estate Law</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" width=69>Need PPN</TD><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 100pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc" width=133 x:num="40299">5/1/10</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl94 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 212pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: #99ccff" width=282 height=34>How many courses are PAST the RELEASE FOR SALE date:</TD><TD class=xl95 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" align=right width=69 x:num>40</TD><TD class=xl39 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 100pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffcc" width=133>'=SUMPRODUCT(--(Y3:Y90>TODAY()))</TD></TR></TBODY></TABLE>

 

[Colin Legg]

I need to count how many courses are past their estimated release date, starting with today's date.

Right, so the way I see it, you want to count when the product release dates are less than today, not greater than today.

=COUNTIF(Y3:Y90,"<"&TODAY())

You could put =TODAY() in a separate cell and then reference that cell from your COUNTIF() formula:

=COUNTIF(Y3:Y90,"<"&A1)

 

[adamkim]

What about using a "COUNTA" and adding a nested function to not count the blank cells. Would this work and if so how would it look?

 

[adamkim]

Nevermind, it worked!! Thank you! Thank you!