Easy way to count cells within date range
 

Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

Easy way to count cells within date range
 

How about:
=SUMPRODUCT(--(G1:G466=K1),--(G1:G466<=K2))

Where the dates are in G1:G466 and the comparison dates are in K1 and K2.

--
tj


"wurstfreund" wrote:

Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

Easy way to count cells within date range
 

With your dates in say A1:G20, and the two date paremeters in say A26 and
A27 (Earliest in A26, latest in A27)

=COUNTIF(A1:G20,""&A25)-COUNTIF(A1:G20,"="&A26)

This will count dates that fall between those two.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"wurstfreund" wrote in message
...
Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

Easy way to count cells within date range
 

The easiest way is either

=COUNTIF(A2:A100,"="&DATE(2006,1,1))-COUNTIF(A2:A100,""&DATE(2006,3,31))

=SUMPRODUCT(--(A2:A100=DATE(2006,1,31)),--(A2:A100<=DATE(2006,3,31)))

will count dates from Jan 1st 2006 to Mar 31st 2006 in A2:A100




--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"wurstfreund" wrote in message
...
Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

Easy way to count cells within date range
 

Here's one way:

With a list of dates in A1:A100

B1: Start date for the date range
B2: End date for the date range

C1: =SUMPRODUCT(--(((A1:A100<=B2)*A1:A100)=B1))
Returns the count of dates in A1:A100 that are within the date range defined
by B1:B2.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"wurstfreund" wrote:

Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?

Easy way to count cells within date range
 

Its hard to beat COUNTIF():

=COUNTIF(A:A,"<upperlimit")-COUNTIF(A:A,"<lowerlimit")
--
Gary's Student


"wurstfreund" wrote:

Here's a challenge (perhaps):
My boss wants me to come up with the easiest way possible to count the
number of cells containing dates on a spreadsheet that fall within a date
range.
The only ways I might typically do this would involve using DCOUNT() or
COUNTIF(), both of which are a tad unweidly. Is there some easier way of
doing this?