Prev: Non-contiguous range and Sumproduct to average
Next: excel formula
From: Roy on 11 Apr 2010 16:56
Using 2003, I am entering dates and trying to utilize a formula to calculate
the number of cells that fall before a certain date.

<"Today()-300", calculates the cells that are in the range.

I have tried the countif and sumif formulas, but they do not like ranges
within the criteria as noted above.

Tried an aray formula without getting it to work.

Any help would be appreciated.
From: Bob Phillips on 11 Apr 2010 17:08
Try

=COUNTIF(A:A,"<-="&TODAY()-300)

--

HTH

Bob

"Roy" <Roy(a)discussions.microsoft.com> wrote in message
news:CD638CA7-95C9-4266-93D7-89900A15CE4B(a)microsoft.com...
> Using 2003, I am entering dates and trying to utilize a formula to
> calculate
> the number of cells that fall before a certain date.
>
> <"Today()-300", calculates the cells that are in the range.
>
> I have tried the countif and sumif formulas, but they do not like ranges
> within the criteria as noted above.
>
> Tried an aray formula without getting it to work.
>
> Any help would be appreciated.


From: ozgrid.com on 11 Apr 2010 22:25
=COUNTIF(A1:A100,"<" & TODAY()-300)

Or, my prefernce would be: =TODAY()-300 in any cell (say B1) and use;
=COUNTIF(A1:A100,"<" & B1)



--
Regards
Dave Hawley
www.ozgrid.com

"Roy" <Roy(a)discussions.microsoft.com> wrote in message
news:CD638CA7-95C9-4266-93D7-89900A15CE4B(a)microsoft.com...
> Using 2003, I am entering dates and trying to utilize a formula to
> calculate
> the number of cells that fall before a certain date.
>
> <"Today()-300", calculates the cells that are in the range.
>
> I have tried the countif and sumif formulas, but they do not like ranges
> within the criteria as noted above.
>
> Tried an aray formula without getting it to work.
>
> Any help would be appreciated.

 | 
Pages: 1
Prev: Non-contiguous range and Sumproduct to average
Next: excel formula