Easy way to count cells within date range

[Guest]

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?

 

[Guest]

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.

 

[Ken Wright]

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
------------------------------­------------------------------­----------------

 

[Peo Sjoblom]

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

 

[Guest]

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

 

[Guest]

Its hard to beat COUNTIF():

=COUNTIF(A:A,"<upperlimit")-COUNTIF(A:A,"<lowerlimit")