ref date works though =TODAY() doesn't?

[mlangen]

=COUNTIF(X11:X16,"<4/15/2011")

This works, though if I change it to refferance a cell that has =TODAY() it doesn't?

Ideally I want to set some cells with different date functions like:
=TODAY -1
=TODAY -3

All I want to do is count any of the Assigned where the date is < TODAY() -7

<TABLE style="WIDTH: 99pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=132><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2318859 class=xl65 height=20 width=64>Assigned</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=68 align=right>3/24/2011</TD></TR><FORM id=aspnetForm method=post name=aspnetForm action=http://office.microsoft.com/en-us/excel-help/use-the-countifs-function-in-excel-2007-to-analyze-data-HA010236993.aspx>

</FORM><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Assigned</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4/7/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Opened</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4/7/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Closed</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3/31/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Resolved</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3/31/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Resolved</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3/31/2011</TD></TR></TBODY></TABLE>

 

[jasonb75]

Try

=countif(x11:x16,"<"&today()-7)

although that will only count the dates, without considering the criteria of "Assigned"

Assuming "Assigned", etc in column W

=sumproduct(--(W11:W16="Assigned"),--(X11:X16<(today()-7)))

 

[Warship]

I'm not sure I fully understand.
Does this help?:
=COUNTIF(X11:X16,"<" & TODAY()-7)

 

[Jonmo1]

Try

=COUNTIF(X11:X16,"<" & A1)

A1: =TODAY()-7

 

[mlangen]

Perfect
=sumproduct(--(W11:W16="Assigned"),--(X11:X16<(today()-7)))

 

[mlangen]

Why does it count the empty rows if I change it to:

=SUMPRODUCT(--(E:E<>"closed"),--(Q:Q<(TODAY()-7)))
Gives me 1048531

though if I specify just the rows with data I get the correct number = 12
=SUMPRODUCT(--(E1:E57<>"closed"),--(Q1:Q57<(TODAY()-7)))

 

[jasonb75]

E:E<>"closed"

Looks for anything not equal to "closed", an empty cell is not equal so will be counted.

Q:Q<(TODAY()-7)

Comparing numeric values, empty cells are zero, which will be less than today()-7 so will be counted.

If you want to use an expanded range to cover future entries you need to exclude blanks with such criteria.

Alternatively, if I remember correctly, you couldn't use entire columns in sumproduct with excel 03, suggesting oyu have a more recent version, so you should be able to use countifs

=COUNTIFS(E:E,"<>closed",Q:Q,"<"&TODAY()-7)